DAX Help - RANKX

[lwklwk98]

Hi PowerBi Community!

I am having an issue with writing a DAX to create a column to rank the values of the overall sales table.

Company	2020 Sales	2020 Rank	2021 Sales	2021 Rank	2022 Sales	2022 Rank
A	10,000,000	1	10,000,000	1	7,000,000	2
B	5,500,000	2	1,500,000	4	800,000	5
C	1,000,000	4	5,000,000	2	10,000,000	1
D	5,000,000	3	1,000,000	5	1,000,000	4
E	700,000	5	3,000,000	3	5,000,000	3

The dataset I have is the problem since it shows multiply entries of Sales for each company. (Each company has 5-10 Sales entries.)

The values in the above table are total yearly Sales and PowerBI has kindly categorized and sum them for each company.

 I searched online and I found solutions that asked me to use Rankx but those works for tables with only singular entries. Is there any way in PowerBi to achieve the ranking for multiple entries?

Thank you so much! Any help or alternative solution to achieve similar results will be appreciated!

[amitchandak · Accepted solution]

@lwklwk98 , is 2020 sales is column or 2020 is year as row

You need create a measure 

sales  = sum(Table[2020 sales])

Rank 2020 = rankx(all(Table[company]), [sales],,desc)

if sales is just a column same rank will work across year

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

[lwklwk98]

@amitchandak 

Thanks so much! It worked like a charm 🙂