Filter sales on dates where every customer has a recorded purchase

[Anonymous]

@Amit 

Hi,

I have a standard model, with a transaction table, a date table and a customer table (10 customers).

What I need is to calculate sales, but only for days where every customer have a recorded purchase.

That means that if I have no customers selected in my slicer, only sales for days where all 10 customers made a purchase should be filtered, if I select 2 customers in my slicer only sales for days where both these 2 customers made a purchase should be filtered, and so on.

This needs to be done dynamic, so I have so far created the following measures:

1. Sales = SUM ( TransactionTable[Value] )
2. Number of Customers Filtered = COUNTROWS ( VALUES ( CustomerTable[Customer] ) )
3. Number of Customers in TransactionTable = COUNTROWS ( VALUES ( CustomerTable[Customer] ) )
4.  Sales Filtered = CALCULATE(
   [Sales] ,

   FILTER(

   VALUES( '01_FACT_Forecast_SpotPrice'[ValueDateCET] ) ,

   [Number of Customers in TransactionTable] = [Number of Customers Filtered]

   )

   )

This provides me with the following result:

So far so good, Sales Filtered excludes datetimes where there is only 1 customer (I have choosen 2 customers in this example).

The problem emerges when I want to add customer as a dimension. Then I only get the total, no split on individual customers:

Clearly, my approach is not correct, so any help would be much appreciated

[Anonymous · Accepted solution]

I found a solution:

1. Sales = SUM ( TransactionTable[Value] )
2. Number of Customers Filtered = CALCULATE(
   COUNTROWS (
   VALUES ( CustomerTable[Customer] )
   ) ,
   
   ALLSELECTED( CustomerTable[Customer] )

   )
3. Number of Customers in TransactionTable = CALCULATE(
   COUNTROWS (
   VALUES ( TransactionTable[Customer] )
   ) ,
   
   ALLSELECTED( CustomerTable[Customer] )

   )
4.  Sales Filtered = CALCULATE(
   [Sales] ,

   FILTER(

   VALUES( Date[Date] ) ,

   [Number of Customers in TransactionTable] = [Number of Customers Filtered]

   )

   )

   Thank you to everyone for posting

[amitchandak]

@Anonymous , Try measures like

Sales = SUM ( TransactionTable[Value] )

Number of Customers Filtered = calculate(COUNTROWS ( VALUES ( CustomerTable[Customer] ) ), all(CustomerTable) )

Sales Customer Date= calculate(countx(filter(values(CustomerTable[Customer]), [Sales]>0), [Customer]), allexcept(TransactionTable,CustomerTable[Customer], 'Date'[Date]))

Number of Customers in TransactionTable = countx(filter(addcolumns(summarize(TransactionTable,CustomerTable[Customer], 'Date'[Date]) , "_1", [Sales] ,
[_2], [Sales Customer Date]), [_2] = [Number of Customers Filtered]),[_1])

[Anonymous]

Hi @amitchandak , and thank you for a hasty reply.

The solution did not work unfortunetly. This was the result:

1. I could not use the ALL function in Number of Customers Filtered (I would only have recieved 10 as a result, not 2 as was filtered).
2. Sales Customer Date seems to generate the exact same result as Number of Customers Filtered
3. Number of Customers in Transaction Table seems to generate the exact same result as previously

I think the problem lies with my measure "Sales Filtered", which does not seem able to filter Sales correctly in response to dates where Customers Filtered and Number of Customers in Transaction Table is the same (2 in my example, since I filtered on 2 customers).

What do you think?

[amitchandak]

@Anonymous , Got lost in this one, we should not include customer in 2nd one

Sales Customer Date= calculate(countx(filter(values(CustomerTable[Customer]), [Sales]>0), [Customer]), allexcept(Date, 'Date'[Date]))

So, means distinct customer on that date

or

Sales Customer Date= calculate(countdistinct(CustomerTable[Customer]), allexcept(Date, 'Date'[Date]))

I was using all in first one to get 10, So that we can check for each date we have only 10 customer 

Thrid one we can also try like

Number of Customers in TransactionTable = countx(Values('Date'[Date]) ,
If( [Sales Customer Date] = [Number of Customers Filtered], [Sales], blank()) )

[Anonymous]

Hi @amitchandak ,

Still no luck.

The problem is that the measure number 4 (Sales Filtered), which is the measure that I need to find the sales on dates where all customer have made a purchase, are able to filter in a "total context", but not in a "subtotal context".

The matrix shows that values for the second customer is still apperent, even though they are filtered out in the total context. These are the values I need to remove/filter out

[smpa01]

@Anonymous  provide a sample pbix