How to Rank and Custom Color top 2, 3-4 and 5-6 ranks dynamically using Dax

[ksrini]

Hi All,

I have several summary tables that show Company wise summary across years per each metric. I need to custom color the top 2, top 3-4, top 5-6 ranks in each year and this applies to each metric which has separate table. For one table, I have added a rankx measure and applied conditional formatting based on it. Each year has different dataset, so I had to create 4 different measures.  If i follow same method, i may have to create 20-24 measures. How can i do the same in DAX for all metrics without creating those many measures and conditional formattings ? I can combine datasets but there are lot of calculations that are specific to each year. Request your help in this.

Sales Comparison
	2019	2018	2017	2016			Metrics to Show
Company 6	3993	1623	1274	2887			Sales
Company 12	3873	2856	2653	2463			Profits
Company 5	3085	2700	2303	2723			Total expenses
Company 8	2926	1972	1076	1582			Current Ratio
Company 2	2773	1469	1825	2431
Company 7	2610	1740	1685	2821
Company 13	2566	1683	2615	2971			Rank
Company 9	2140	2629	1362	2771			Top 2
Company 10	1948	1830	2148	2970			Top 3-4
Company 1	1860	1027	3470	2547			Top 5-6
Company 14	1647	1025	1597	1661			Others
Company 17	1464	2033	1029	2945
Company 3	1330	1413	1858	2160
Company 16	1279	2766	2604	1491
Company 15	1189	2746	1871	1638
Company 4	1133	2899	1921	1012
Company 11	1041	1091	2142	1033

Thanks,

Srinivas

[v-lili6-msft · Accepted solution]

hi @ksrini 

You need to unpivot all the year columns into one year, then show them in a matrix and set year filter separately.

https://radacad.com/pivot-and-unpivot-with-power-bi

In this way, you just need to create 4 different measures, otherwise, you need to create 20-24 measures.

Regards,

Lin

[ksrini]

Thanks Lin. Followed the approach to create fewer measures. 

[amitchandak]

@ksrini ,

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
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...