Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

RANK - How can i do this??

Hwlloe, i would like to know how to apply rank in this example, i have created a kpi and category measure. I want to know how to do the top 3 highest of category 1 and the lowest top 3 of category 2.

 

I have this

citykpicategory

city 1

1001
city 2

200

2
city 33001
city 44001
city 55002
city 66002
city 77002
city 88001

i want this

 

citykpicategoryrank
city 880011
city 440012
city 330013
city 660021
city 550022
city 220023

Thanks!

General Comment
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi Anonymous ,

     

    Based on your description, KPI and category are measures.

    Try to create a measure like this and apply it to visual level filter. 

    Measure =
IF (
    [category Measure] = 1,
    RANKX (
        FILTER ( ALLSELECTED ( 'Table'[city] ), [category Measure] = 1 ),
        [Kpi Measure],
        ,
        ASC,
        DENSE
    ),
    IF (
        [category Measure] = 2,
        RANKX (
            FILTER ( ALLSELECTED ( 'Table'[city] ), [category Measure] = 2 ),
            [Kpi Measure],
            ,
            DESC,
            DENSE
        )
    )
)

    Sample .PBIX 

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

    • V-lianl-msft's avatar
      V-lianl-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi Anonymous ,

       

      Based on your description, KPI and category are measures.

      Try to create a measure like this and apply it to visual level filter. 

      Measure =
IF (
    [category Measure] = 1,
    RANKX (
        FILTER ( ALLSELECTED ( 'Table'[city] ), [category Measure] = 1 ),
        [Kpi Measure],
        ,
        ASC,
        DENSE
    ),
    IF (
        [category Measure] = 2,
        RANKX (
            FILTER ( ALLSELECTED ( 'Table'[city] ), [category Measure] = 2 ),
            [Kpi Measure],
            ,
            DESC,
            DENSE
        )
    )
)

      Sample .PBIX 

       

      Best Regards,
      Liang
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Thank you so much! it really works

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Please find the attached file after the signature.  Rank and filter on Rank have been used.

     

     

     

    Measure = RANKX(ALL('Table (2)'[city]),CALCULATE(sum('Table (2)'[kpi])))

You can also try top N
Top 10 City Rank = CALCULATE(CALCULATE(sum('Table (2)'[kpi])),TOPN(10,all('Table (2)'[City]),CALCULATE(sum('Table (2)'[kpi])),DESC),VALUES('Table (2)'[City]))