Distinct Count of Customers that Purchased More than X

[Anonymous]

Hi,

Here’s my problem. I want to have a gauge that shows customers that spent over $10 and bought product B / customers that spent over $10. It’s a direct query model and I can’t add any tables. I can only do custom columns and measures. The real kicker is that the data model is one big fact table so i have to roll up all the customers purchases to see if they’re over $10.

Any ideas?? I was thinking basically 2 custom columns that flag if a customer purchased over $10 and if they bought product B. The DQ model is proving to be difficult though. Any help/ideas is appreciated. Below is some mock up data I created. In this situation it should be 66% penetration for Q1 as Customer 1 and 2 both spent $10 and bought product B but Customer 3 spent enough but didn't buy product B. Customers 4 and 5 didn't spend enough or buy product B. 

[Greg_Deckler]

The general pattern should be something like:

Measure =
  VAR __Table = 
    SUMMARIZE(
      'Table',
      [Customer ID],
      [Product],
      "__Sales",SUM('Table'[Sale])
    )
RETURN
  COUNTROWS(
    DISTINCT(
      SELECTCOLUMNS(
        FILTER(
          __Table,
          <your criteria>
        ),
        "Customer ID",
        [Customer ID]
       )
     )
   )
       
    

[Anonymous]

Thanks Greg. This is what I have so far. Basically now im getting an error saying "a table of multiple values was supplied where a single value was expected. Im guessing it has something to do with my "in {c}" syntax

Customer Penetration Rate =

//target customer table

VAR __Table =

    SUMMARIZE ( 'Table', [Customer ID], "__Sales", SUM ( 'Table'[Sale] ) )

//counting total target customers

VAR a =

    COUNTROWS (

        DISTINCT (

            SELECTCOLUMNS (

                FILTER ( __Table, [__Sales] >= 10 ),

                "Customer ID", [Customer ID]

            )

        )

    )

//total target  customer IDs

VAR c =

    SELECTCOLUMNS (

        FILTER ( __Table, [__Sales] >= 10 ),

        "Customer ID", [Customer ID]

    )

//penetrated customer table

VAR __Table2 =

    SUMMARIZE ( 'Table', [Customer ID], [Product] )

//count of penetrated customers

VAR b =

    COUNTROWS (

        DISTINCT (

            SELECTCOLUMNS (

                FILTER ( __Table2, [Customer ID] IN { c } && [Product] = "B" ),

                "Customer ID", [Customer ID]

            )

        )

    )

//divide num of penetrated customers by num of target customers

RETURN

    DIVIDE ( b, a, 0 )

[Anonymous · Accepted solution]

@Anonymous 

You do not need the curly brackets after "IN", because you always expect a table expression after IN function. And the variable c  = SELECTCOLUMNS() itself is a table expression, it is unnecessary to add another table constructor{} to variable c. 

Measure = 
//target customer table
VAR __Table =SUMMARIZE ( 'Table', [Customer ID], "__Sales", SUM ( 'Table'[Sale] ) )

//counting total target customers
VAR a = COUNTROWS (DISTINCT (SELECTCOLUMNS (FILTER ( __Table, [__Sales] >= 10 ),"Customer ID", [Customer ID])))
 
//total target  customer IDs
VAR c =SELECTCOLUMNS (FILTER ( __Table, [__Sales] >= 10 ),"Customer ID", [Customer ID])
 
//penetrated customer table
VAR __Table2 = SUMMARIZE ( 'Table', [Customer ID], [Product] )
 
//count of penetrated customers
VAR b = COUNTROWS (DISTINCT (SELECTCOLUMNS (FILTER ( __Table2, 'Table'[Customer ID] in c && [Product] = "B" ),"Customer ID", [Customer ID])))
 
//divide num of penetrated customers by num of target customers
RETURN DIVIDE ( b, a , 0)

Best, regards
Paul Zheng