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.
Hi All,
I need help in calculating "Average of bottom 95% values in a column".
Thank you in advance for the support!
Regards,
Salman
Solved! Go to Solution.
Hi @Anonymous. Define "bottom". I'm going to assume you mean the 95% of records with the lowest values, or discard those records with the highest values.
Here's what you do:
Here's some code from something similar that I did...
VAR PercentileCutoff =
PERCENTILEX.INC ( CalculateDaysToSubmission, [DaysToSubmission], .99 )
RETURN
AVERAGEX (
FILTER (
CalculateDaysToSubmission,
[DaysToSubmission] >= 0
&& [DaysToSubmission] <= PercentileCutoff
),
[DaysToSubmission]
)
Hope this help! 🙂
Hi @Anonymous. Define "bottom". I'm going to assume you mean the 95% of records with the lowest values, or discard those records with the highest values.
Here's what you do:
Here's some code from something similar that I did...
VAR PercentileCutoff =
PERCENTILEX.INC ( CalculateDaysToSubmission, [DaysToSubmission], .99 )
RETURN
AVERAGEX (
FILTER (
CalculateDaysToSubmission,
[DaysToSubmission] >= 0
&& [DaysToSubmission] <= PercentileCutoff
),
[DaysToSubmission]
)
Hope this help! 🙂
Hi @littlemojopuppy , thank you for a quick response. And you were right, I wanted to skip out highest values. However, the code you provided is not working for me, for some reason it gives error from "RETURN" till the end. I am not sure what I did wrong, hoping you can help:
Hi @Anonymous did you name the measure? It doesn't look like there's a name
@Anonymous you'rew welcome! Glad I could help! 🙂
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 |
---|---|
116 | |
104 | |
77 | |
71 | |
50 |
User | Count |
---|---|
145 | |
108 | |
107 | |
90 | |
65 |