Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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.
yes, as I mentioned - Change the first row according to your measure names.
Apologies, I have a follow up.
I'm trying to verify the numbers and I am getting stuck. The first variable creates a table for customer name with the 1st four quarters, 2nd four quarters, and 8 quarters revenue are tied together. The second variable creates a filter reduce the first variable down. The final formula takes the filtered table and compares any customer that had greater than 0 revenue in the 2nd 4 quarters and divides it by any customer that had greater than 0 revenue in the 1st 4.
Do I have that right?
My manual numbers are coming out different than the formula.
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.
This requirement somewhat conflicts with the next explanations so I re-interpreted it as "Count all customers that had sales in the first 4 quarters, and independently count all customers that had sales in the last four quarters". Taken literally, your requirement would always result in 100%.
Ah, okay. The intention is to get a list (1) of customers that over 8 quarters had greater than a minimum dollar amount. Then a list (2) of customers in the first 4 quarters with sales greater than 0 and a list (3) of customers in the second four quarters with sales greater than 0. We only care about customers in list 2 and 3 that were in list 1. Of those customers remaining in lists 2 and 3, how many that were in 2 that made it to 3. Then we divide the number of customers in 3 by the number of customers in 2.
In that situation, would the ""2nd c",if"2nd c",if([2nd 4Q]>0,1,0))" formula also need to include
"[1st 4Q]>0"?
Edit:
OR would I need to include two new variables such as:
yes to the pre-edit question. Not sure where you are going with the edit question. would the value ever be less than 0 ?
Thank you so much for the help!
Okay so I updated the formula to be:
The edited question was referring to how we were originally doing the formula manually. We would get list 2 and list 3 and determine New customers (in 3 but not 2) and Lost customers (in 2 but not 3) and confirm they were not counted as part of retained. The retained should not be less than 0, but I did not know if creating a variable noting the New and Lost and subtracting them from the return would be effective. Looking at the formula, that the numerator requires both 2 and 3 to be greater than 0 should make it work.
I have gotten it to work. My issue is my measures around my first four and second four revenue. I am using a base of Revenue CQ = CALCULATE(SUM(revenue), DATESQTD(Calendar))) to setup the current period and then using DATEADD to get the additional 7 quarters: PQ = Calculate(Revenue CQ, DATEADD(Calendar,-1,Quarter)).
This was screwing up the revenue being selected as the Summarized table wasn't getting the right time period. When I hardcoded the time periods using DATESINPERIOD() my numbers came out correct. I'll be working on getting the First 4, Second 4, and All 8 measures to work so I can choose the time period through a slicer.
Thank you lbendlin for your assistance!
Great! It appears to work, thank you so much. I will add this topic on my list of things to learn more about.
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.
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |