cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Filter sales on dates where every customer has a recorded purchase

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
6 REPLIES 6
Anonymous
Not applicable

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
Super User

@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
Not applicable

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?

Super User

@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
Not applicable

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

Super User

@Anonymous  provide a sample pbix

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors