RANK - How can i do this??

[Anonymous]

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

city	kpi	category
city 1	100	1
city 2	200	2
city 3	300	1
city 4	400	1
city 5	500	2
city 6	600	2
city 7	700	2
city 8	800	1

i want this

city	kpi	category	rank
city 8	800	1	1
city 4	400	1	2
city 3	300	1	3
city 6	600	2	1
city 5	500	2	2
city 2	200	2	3

Thanks!

[amitchandak]

@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]))
	

[Sumanth_23]

hi @Anonymous - you should be able to achieve this ranking by following the steps in the below post

https://community.powerbi.com/t5/Desktop/Sequence-number/m-p/1367645#M585159

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

Regards,
Sumanth

[V-lianl-msft · Accepted solution]

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]

Thank you so much! it really works