cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
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

@dragonus 

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?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

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

View solution in original post

10 REPLIES 10
Super User II
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. 


If you found this post helpful, please give Kudos.


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


excelwithallison.com

@AllisonKennedy 

 

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

divide denumanator.PNG

Super User II
Super User II

@dragonus 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown @nandic 

 

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

@dragonus 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown 

 

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..

@dragonus 

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?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

@PaulDBrown Apologies, both are the same table. Mistyped Score as Table hahaha

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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