cancel
Showing results for
Did you mean: Helper III

## Can someone help fix my Percentile DAX formula?

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')))``````

2 ACCEPTED SOLUTIONS

Accepted Solutions Super User II
``````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?

In doing so, you are also helping me. Thank you!

Proud to be a Super User! Microsoft

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

10 REPLIES 10 Super User II

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] )
)
)

______________

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

excelwithallison.com Helper III

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 Solution Sage

@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])  Super User II

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])))

In doing so, you are also helping me. Thank you!

Proud to be a Super User! Helper III

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 Super User II

how is your model set up and what are the tables structure?

In doing so, you are also helping me. Thank you!

Proud to be a Super User! Helper III

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.. Super User II
``````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?

In doing so, you are also helping me. Thank you!

Proud to be a Super User! Helper III

@PaulDBrown Apologies, both are the same table. Mistyped Score as Table hahaha Announcements #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella. Top Solution Authors
Top Kudoed Authors
Users online (2,191)