Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I just started using DAX and Power BI. I am attempting to complete 2 actions here, lets say we are dealing with car loans here:
1. Each loan is assigned an expected default value or rate (i.e. Loan 1 has a DefaultRate of 5.25%). I have thousands of these. I need to figure out, for each car dealer( DealerName), what the 95th percentile is for each dealer. Users need to be able to select different time periods (PurchaseMonth) so I want the measure to be able to look at any date period used in say a slicer and adjust the 95th percentile accordingly.
95th Percentile = CALCULATE(PERCENTILE.EXC(MainData[DefaultRate],.95),MainData[DealerName]=MainData[DealerName],MainData[PurchaseMonth]=MainData[PurchaseMonth])
2. Based on that dynamic 95th percentile data point for each DealerName for each PurchaseMonth I need to take the average of the DefaultRate only where DefaultRates > 95th Percentile point. I was able to get this working only if I created a fixed column for the 95th percentile (95thPercentileRate), and then created a second column (DefaultRate5%TailLoan) used to ID the car loans that have default rates > the 95th percentile:
DefaultRate5%TailLoan = IF(MainData[DefaultRateLoan]=0,"N/A",IF(MainData[DefaultRate]>MainData[95thercentileRate],"Y","N"))
I then use the following formula to determine the average of the DefaultRates where the DefaultRate5%TailLoan column = Y
Avg.DefaultRate5%Tail = CALCULATE(AVERAGE(MainData[DefaultRate]),MainData[DefaultRate5%TailLoan]="Y")
I clearly need a lot of help and am willing to learn but I am not finding any answers in forums or training for dax useful in my situation.
Thank you
-James
Solved! Go to Solution.
Pro Tip:
This is for you own sanity -- helps you "remember" what ur talking about when going back to read your fo it "remembering" what you are talkin' about
[quote]
CALCULATE(AVERAGE(MainData[DefaultRate]), FILTER(MainData, MainData[DefaultRate]> [95thercentileRate]))
[/quote]
My guess at what is going wrong is that the call to FILTER() is going to iterate 1 row at a time over MainDate. So, you are going to evaluate [95thPercentileRate] against... 1 row. If we want to compare to the overall 95th percentile, we need to break out of the single row somehow. Maybe a new measure?
95thRate-All := CALCULATE([95thPercentileRate], ALL(MinData))
And use that in the filter?
Before I forget, I'm skeptical of your 95th Percentile measure. I claim that MainData[PurchaseMonth]=MainData[PurchaseMonth] is always true. I would expect Try MainData[PurchaseMonth]=EARLIER(MainData[PurchaseMonth]) or... something?
Anyway, just looking at these two parts...
IF(MainData[DefaultRate]>MainData[95thercentileRate],"Y","N")
CALCULATE(AVERAGE(MainData[DefaultRate]),MainData[DefaultRate5%TailLoan]="Y")
Seems we could combine them:
CALCULATE(AVERAGE(MainData[DefaultRate]), FILTER(MainData, MainData[DefaultRate]>MainData[95thercentileRate]))
Does that do as you hope?
Hi scottsen, The 95th percentile formula does "just work". Thank you
The other part that I am struggling with now is when I try the combined formula as you suggested below:
CALCULATE(AVERAGE(MainData[DefaultRate]), FILTER(MainData, MainData[DefaultRate]>MainData[95thercentileRate]))
I am receiving the following calculation error message
any idea what could be causing this error? Am I able to write a measure based off a separate measure?
Any and all help is appreciated. Thank you!
@Anonymous Any chance you would know what is causing the error message above? I am using the .95 as the k value in the percentile formula, so I am not sure why this error would come up. Also, I dont get this error in the percentile formula by itself, only when I include it in the formula which you suggested.
Thank you for your assistance!
Can you clarify if MainData[95thercentileRate] is a measure or a column?
Hi,
It is a measure. I made it as a measure since this value will adjust depending on the different selections made in slicers/filters/etc.
Pro Tip:
This is for you own sanity -- helps you "remember" what ur talking about when going back to read your fo it "remembering" what you are talkin' about
[quote]
CALCULATE(AVERAGE(MainData[DefaultRate]), FILTER(MainData, MainData[DefaultRate]> [95thercentileRate]))
[/quote]
My guess at what is going wrong is that the call to FILTER() is going to iterate 1 row at a time over MainDate. So, you are going to evaluate [95thPercentileRate] against... 1 row. If we want to compare to the overall 95th percentile, we need to break out of the single row somehow. Maybe a new measure?
95thRate-All := CALCULATE([95thPercentileRate], ALL(MinData))
And use that in the filter?
Boom! You rock sir. Unfortunately using the All function only gave me the results for the entire population. When I switched the function to AllSelected I obtained the correct results!
95thRate-All := CALCULATE([95thPercentileRate],ALLSELECTED(MainData))
Thank you so much!!!
Awesome, Thank you! That solves the second part to my question.
Now I need to figure out a way to make my 95th percentile column dynamic so that it adjusts depending on the PurchaseMonth selected by the user. So if they selected a 6 month period, the 95th percentile would be based on that period, or any other period that may be selected. Is there a way to do this? Im struggling with whether or not this should be a measure or a column.
Thank you
-James
If you write a measure, say just:
95th Percentile = PERCENTILE.EXC(MainData[DefaultRate],.95)
It should "just work".
It will work correctly for 1 region or all regions, 1 month or all months, etc. Measures generally work against any set/subset of rows.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |