RANKX as calculated column based on column

[Anonymous]

Hi everyone! 

I have data in a table with columns such as below:

Country (all unique)	Score (calculated column)
A	5
B	3
C	2

My end goal is to have rank in a column as I need to create other columns off of it:

Country (all unique)	Score (calculated col)	Rank (calculated col)	Top2 (calculated col)
A	5	1	A
B	3	2	B
C	2	3	All Other

I tried: Rank = rankx(Table,[Score]). I also thres in allselected, calculate, sum, etc but am getting all 1s in return. 

Would appreciate any help as I'm a total beginner! Thank you! 

[v-zhangti]

Hi, @Anonymous 

You can try the following methods.

Column:

Rank = RANKX('Table',[Score])

Top2 = IF([Rank]<=2,[Country],"All Other")

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

This worked! However, is there any way to make it dynamic based on whatever filters are on the page? Thanks so much! 

Country (all unique)	Score (calculated col)	Rank (calculated col)	Top2 (calculated col)	Scale
A	5	1	A	Big
B	3	2	B	Small
C	2	3	All Other	Small

[v-zhangti]

Hi, @Anonymous 

According to your description it should be feasible. But I need to know the output you expect, preferably with a picture to show it.

Best Regards

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

The below DAX formulas are for creating new columns.

Rank CC = 
RANKX( Data, Data[Score],,DESC )

Top two CC = 
VAR toptwotable =
    SUMMARIZE ( TOPN ( 2, Data, Data[Score], DESC ), Data[Country] )
RETURN
    IF ( Data[Country] IN toptwotable, Data[Country], "All others" )

[Anonymous]

Thanks for the reply! Is there any way to make this dynamic based on whatever filters are on the page? Appreciate it! 

[Jihwan_Kim]

Hi,

Thank you for your feedback.

I suggest having a measure, instead of creating calculated column.

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

Top two measure = 
VAR toptwotable =
    SUMMARIZE ( TOPN ( 2, ALLSELECTED( Data ), [Score measure:], DESC ), Data[Country] )
RETURN
    IF (
        HASONEVALUE ( Data[Country] ),
        IF (
            VALUES ( Data[Country] ) IN toptwotable,
            VALUES ( Data[Country] ),
            "All others"
        )
    )