Forum Discussion

joyhackett's avatar
joyhackett
Icon for Helper II rankHelper II
5 years ago
Solved

Percentile Rank in Group

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!

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

2 Replies