Forum Discussion
Distinct Count Customers with Filters based on Measures
Hello,
I've only been using Power BI and Dax for a few months and I've reached the stage of having very specific questions
I'm trying to filter a distinct count of customer names by a minimum revenue amount over a selected period to get a total I can use to create a percentage of retained customers.
I have been able to create filters that will filter tables within the desktop dashboard:
1. Customers must have sales over a threshold (ex 100k) in a selected period (8 quarters)
2. Customers must have sales in the first 4 quarters (2021 for example) and the most recent 4 quarters (2022 for example) of greater than 0.
The numerator is filters 1 and 2 for customers in the most recent 4. Denominator is filters 1 and 2 for the first 4.
Example data
| Customer Name | Sales 1st 4Q | Sales 2nd 4Q | Sales all 8 |
| A | 0 | 250000 | 250000 |
| B | 55000 | 665000 | 720000 |
| C | 120000 | 0 | 120000 |
| D | 75000 | 65000 | 140000 |
| E | 35000 | 50000 | 85000 |
| F | 8850 | 987560 | 996410 |
| G | 580000 | 0 | 580000 |
Filter 1 should leave me with 6 customers. Filter 2 will give me 5 customers in 1st 4Q and 4 in 2nd 4Q
Numerator should be = 4 and denominator will be 5 giving me an 80%
I've tried Calculate(), which has issues with boolean logic, such as revenue greater than 100K, which led me to put revenue in a Filter(). I still haven't gotten a number out of it.
Note- the 4 quarter revenue and 8 quarter revenue are derived by measures as my actual revenue is based on individual orders that needs to be summed up. I do want to keep these measures as I want to be able to change the 8 sequential quarters to show progress over time.
Any assistance is appreciated.
Welcome to the world of variables and aggregator functions!
Ratio = var a = SUMMARIZE('Table',[Customer Name],"1st 4Q",sum('Table'[Sales 1st 4Q]),"2nd 4Q",sum('Table'[Sales 2nd 4Q]),"All 8",sum('Table'[Sales all 8])) var b = ADDCOLUMNS(filter(a,[All 8]>=100000),"1st c",if([1st 4Q]>0,1,0),"2nd c",if([2nd 4Q]>0,1,0)) return DIVIDE(SUMX(b,[2nd c]),SUMX(b,[1st c]),0)Change the first row according to your measure names.
10 Replies
Welcome to the world of variables and aggregator functions!
Ratio = var a = SUMMARIZE('Table',[Customer Name],"1st 4Q",sum('Table'[Sales 1st 4Q]),"2nd 4Q",sum('Table'[Sales 2nd 4Q]),"All 8",sum('Table'[Sales all 8])) var b = ADDCOLUMNS(filter(a,[All 8]>=100000),"1st c",if([1st 4Q]>0,1,0),"2nd c",if([2nd 4Q]>0,1,0)) return DIVIDE(SUMX(b,[2nd c]),SUMX(b,[1st c]),0)Change the first row according to your measure names.
Thank you for the help. The only confirmation I need is if this section :
""1st 4Q",sum('Table'[Sales 1st 4Q]),"2nd 4Q",sum('Table'[Sales 2nd 4Q]),"All 8",sum('Table'[Sales all 8]))"
can function as measures instead of summed data fields. I'd like this to be selectable based on the quarter selected. I do have a functioning date table.
