Can someone help fix my Percentile DAX formula?

[dragonus]

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

[PaulDBrown · Accepted solution]

@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?

[PaulDBrown]

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

[dragonus]

@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

[PaulDBrown]

@dragonus 

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

[dragonus]

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

[PaulDBrown · Accepted solution]

@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?

[dragonus]

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

[v-deddai1-msft · Accepted solution]

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

[nandic]

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

[AllisonKennedy]

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

[dragonus]

@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