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.
My current formula below allowed me to calculate the Percentiles for the values that I have. However, it took into account the entire range of values & rank within my table and not just within their own categories. So instead of creating percentiles within A/B/C separately, it created percentiles encompassing all of A/B/C.
How should I fix this so the percentiles are calculated within their own categories?
Percentile = DIVIDE(
CALCULATE (
COUNTX( 'Z-Table', 'Z-Table'[Value]),
FILTER('Z-Table','Z-Score'[Rank] > EARLIER ('Z-Score'[Rank]))),
CALCULATE(COUNT('Z-Score'[Value]),ALL('Z-Score')))
Solved! Go to Solution.
Percentile = DIVIDE(
CALCULATE (
COUNTX( 'Z-Table', 'Z-Table'[Value]),
FILTER('Z-Table','Z-Score'[Rank] > EARLIER ('Z-Score'[Rank]))),
CALCULATE(COUNT('Z-Score'[Value]),ALL('Z-Score')))
there seem to be 2 tables referenced in your measure: Z-Table (COUNTX ('Z-Table'...) and Z-Score (ALL('Z-Score')
can you post a sample table with fake data?
Proud to be a Super User!
Paul on Linkedin.
Hi @dragonus ,
Are you trying to compute Percentile by category?If so, please refer to :
Percentile =
DIVIDE (
CALCULATE (
COUNT ( 'Z-Table'[Value] ),
FILTER (
ALLEXCEPT ( 'Z-Table', 'Z-Table'[Category] ),
'Z-Score'[Rank] > EARLIER ( 'Z-Score'[Rank] )
)
),
CALCULATE (
COUNT ( 'Z-Score'[Value] ),
ALLEXCEPT ( 'Z-Score', 'Z-Score'[Category] )
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Try using ALLEXCEPT instead of ALL:
Percentile = DIVIDE(
CALCULATE (
COUNTX( 'Z-Table', 'Z-Table'[Value]),
FILTER('Z-Table','Z-Score'[Rank] > EARLIER ('Z-Score'[Rank]))),
CALCULATE(COUNT('Z-Score'[Value]),ALLEXCEPT('Z-Score', Z-Score[Category])))
Proud to be a Super User!
Paul on Linkedin.
I've tried using ALLEXCEPT, but the end result returned me numbers that are from the 0.000s to the thousands.... Not too sure why is this so... previously my percentiles are all in the 0.0 to 0.9s so I could use it to calculate appropriate categories for them later on, which is what I wanted
how is your model set up and what are the tables structure?
Proud to be a Super User!
Paul on Linkedin.
Not too sure what you mean, this table is a standalone table, not connected with any other tables.
My table has 6 columns, namely Date/Category/Value/Rank (Created Column)/Percentile (Created Column)/ Bins (created column).
Date: Historical, from 2000 to today, in weekly fashion
Category: Names of different States (eg Alabama/Florida/Wisconsin/etc)
Value: How much they earn in a week, as values
Rank: Used RANKX to create a ranking within each state, so Alabama will have their own 1 to X for example
Percentile: Wanted to use it to measure how does the Rank on a particular date fare against the ranks of other dates in a certain state
Bins: This is based on Percentile, where they are allocated to bins of 10 (eg 0 - 10/10-20/etc)
Hope this is clear enough..
Percentile = DIVIDE(
CALCULATE (
COUNTX( 'Z-Table', 'Z-Table'[Value]),
FILTER('Z-Table','Z-Score'[Rank] > EARLIER ('Z-Score'[Rank]))),
CALCULATE(COUNT('Z-Score'[Value]),ALL('Z-Score')))
there seem to be 2 tables referenced in your measure: Z-Table (COUNTX ('Z-Table'...) and Z-Score (ALL('Z-Score')
can you post a sample table with fake data?
Proud to be a Super User!
Paul on Linkedin.
Hi @dragonus ,
Are you trying to compute Percentile by category?If so, please refer to :
Percentile =
DIVIDE (
CALCULATE (
COUNT ( 'Z-Table'[Value] ),
FILTER (
ALLEXCEPT ( 'Z-Table', 'Z-Table'[Category] ),
'Z-Score'[Rank] > EARLIER ( 'Z-Score'[Rank] )
)
),
CALCULATE (
COUNT ( 'Z-Score'[Value] ),
ALLEXCEPT ( 'Z-Score', 'Z-Score'[Category] )
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@dragonus ,
In last step where you calculate denominator, you should use function AllExcept to group values by category.
If you want to calculate denominator by group use AllExcept(table, category).
Example when to use it: you have values by month and you would like to find percentage of each month in year total.
Numerator = sum(amount) -- monthly
Denumerator = calculate([Numerator], AllExcept(table, year)) -- yearly
% of year = divide ([Numerator],[Denumerator])
It would help to know what your data columns are, but basically you need to change the denominator of your divide function.
Using https://www.daxformatter.com/ to format your current formula you can see better where the denominator starts and that I have updated the ALL() to only remove filters on the current category, not the whole Z table.
Percentile =
DIVIDE (
CALCULATE (
COUNTX (
'Z-Table',
'Z-Table'[Value]
),
FILTER (
'Z-Table',
'Z-Score'[Rank]
> EARLIER ( 'Z-Score'[Rank] )
)
),
CALCULATE (
COUNT ( 'Z-Score'[Value] ),
ALL ( 'Z-Score'[Category] )
)
)
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
My Data columns are Date/Category/Value/Rank (Created Column)/Percentile (Created Column)/ Bins (created column)
The Bins are essentially categorizing the percentiles into bins of +10 percentiles
I did what you mentioned (adding in [Category] for ALL) , but it returned that there was a circular dependency... weird
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |