Dynamic percentile ranks across two tables

[veronika]

HI All, 

I have a question about calculating percentile rankings.

I have two tables, one with products, their categories and various metrics, one of which is Return. Second table is for hierarchy of the categories. 

I need to create a visual with slicers using the category hierarchy values (Group and Sub-group) and a table underneath with the individual products, their return and also percentile ranking or the return based on what is selected in the slicers. That means the percentage ranking would recalculate depending on whether I select only Group or Sub-Group or the end Category. Any thoughts on how to implement that?

Thank you very much, 

Veronika

[v-jiascu-msft]

Hi @veronika,

Can you post an expected result? Please try the demo in the attachment. 

Measure =
CALCULATE (
    PERCENTILE.INC ( Products[Return], 0.5 ),
    ALL ( Products[Product] )
)

Best Regards,

Dale

[veronika]

Hi Dale,

Thanks for the demo, you nearly got what I failed to explain properly. In your result the measure is the same for all products, I assume giving the 50th percentile value. I need the individual percentile ranks for all the products which make it to the table based on the slicers. The values would be from 1-100 (or 0-100) and would differ for all products unless they had the same return. the below is done in excel using "percentrank.inc" function.

Many thanks,

Veronika

[v-jiascu-msft · Accepted solution]

Hi Veronika,

It's ranks rather than percentile in your scenario. Please try the formula below. Please also refer to PERCENTRANK-Inclusive/td-p/81208.

Measure 2 =
VAR ranks =
    RANKX (
        ALLSELECTED ( Products[Product] ),
        CALCULATE ( SUM ( Products[Return] ) ),
        ,
        ASC
    )
VAR allSelectedAmount =
    CALCULATE ( COUNT ( Products[Product] ), ALLSELECTED ( Products[Product] ) )
RETURN
    ( ranks - 1 )
        / ( allSelectedAmount - 1 )
        * 100

Best Regards,

Dale