cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Announcements

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors