Percentile Rank in Group

[joyhackett]

Hi,

I've been looking at the other posts for Percentiles, but I am not finding a similar scenario, so I'd appreciate any guidance.

I have a fact table with Category, Subcategory, Item, and Count. 

There is an Item dimension table but it is NOT RELATED to the fact table.

The user must select a Item from a slicer (using the Item dimension table) and a card will show it's subcategory percentile rank.

Can anyone help with the Percentile Rank measure DAX?

Thanks!

[v-jingzhang · Accepted solution]

Hi @joyhackett 

You can create two calculated columns in the table and then a measure to get the percentile value.

Two columns are:

Subcategory Rank = 
RANKX (
    FILTER (
        'Table',
        'Table'[Subcategory] = EARLIER ( 'Table'[Subcategory] )
    ),
    'Table'[Count],
    , // leave value argument blank
    DESC
)

PercentileRank = 
VAR _total =
    COUNTROWS (
        FILTER ( 'Table', 'Table'[Subcategory] = EARLIER ( 'Table'[Subcategory] ) )
    )
RETURN
    DIVIDE ( _total + 1 - 'Table'[Subcategory Rank], _total + 1 )

Measure:

Percentile = 
MAXX (
    FILTER ( ALL ( 'Table' ), 'Table'[Item] = SELECTEDVALUE ( 'Item List'[Item] ) ),
    'Table'[PercentileRank]
)

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Greg_Deckler]

@joyhackett See this: (1) Solved: PERCENTRANK (Inclusive) - Microsoft Power BI Community

Also, see this:

Excel to DAX Translation - Microsoft Power BI Community