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

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
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.