Customer List with invoice which has all selected items

Question

I have DimProduct table, DimCustomer table and FactSales table. There is a targeted product list. I want to list customers who buy all products of the targeted product list in one invoice. How do I do it? I have no clue. Please give me some advice.

Answer 1

CustomersWithAllTargets:=
COUNTROWS(
    FILTER(
        DimCustomer
        ,CALCULATE(
            DISTINCTCOUNT( FactSale[ProductKey] )
            ,TargetProduct
        ) = DISTINCTCOUNT( TargetProduct[ProductKey] )
    )
)

Let's break this down.

COUNTROWS() does what it says and counts the rows in a table.

The table whose rows we want to count is the result of our FILTER(). FILTER() takes a table expression as its first argument, creating a row context by iterating over each row in that table expression. For each row, the expression in its second argument is evaluated. Only those rows in the table for which the second argument evaluates to true are included in the output.

Our table argument is DimCustomer. DimCustomer will be filtered by the pivot's filter context (e.g. if you select a subset of customers, only that subset will be considered).

For each customer, we evaluate the CALCULATE().

CALCULATE() evaluates its first argument in the filter context defined by its second through last arguments. We're counting the distinct values in FactSale[ProductKey] for the current customer (current in the iteration through each row from FILTER()), subject to the constraint that they exist in the table TargetProduct.

We are testing the value of that CALCULATE() (how many target products the current customer has bought), with the count of values in TargetProduct[ProductKey]. When they are equal, the customer has bought all products. When they are not equal, the customer hasn't.

So we'll return a table of customers who've bought all the target products. At the customer level in a pivot, this will return 1 or blank for each customer. Pivot tables automatically suppress the display of row labels with blanks for measures, so you'll only see the customers that have bought all the target products.

The grand total will tell you how many customers total have bought all the targets.

This will also support selecting subsets of TargetProduct, if you have different target groups.

Below is an image of my model and sample data, along with a pivot table showing the whole thing behaving appropriately.

Edit

We'll make use of one more function to be able to group by multiple fields, SUMMARIZE().

CustomersWithAllTargets:=
COUNTROWS(
    FILTER(
        SUMMARIZE(
            FactSale
            ,FactSale[InvoiceKey]
            ,FactSale[CustomerKey]
        )
        ,CALCULATE(
            DISTINCTCOUNT( FactSale[ProductKey] )
            ,TargetProduct
        ) = DISTINCTCOUNT( TargetProduct[ProductKey] )
    )
)

SUMMARIZE() takes a table as its first argument and a list of fields to group by. It can also add calculated columns and do rollups on those columns, but we don't need that. Newer versions of Power Pivot (Excel 2016) have a syntax-equivalent GROUPBY() that performs grouping for a potential (small) performance improvement. We simply group on InvoiceKey and CustomerKey and do the same filtering as before.

This does alter the nature of the return value. Previously the count would be the number of customers who've purchased the whole set of target products. Since we're now also grouping on invoice, the count will be of the number of customer-invoice pairs that have all targets purchased. Since your requirement was stated simply as listing the customers, this measure still meets them. You may see a number >1 for a specific customer, though. You will still have the behavior in a pivot of omitting the customers who don't meet the criteria.

Here's a picture of my altered sample data performing with this measure. Note that Customer6 now has purchased all three targets, but on separate invoices. Customer3 still shows up as all three are on 1 invoice.