Ranking in Matrix

[dallison]

Hi All - 

I'm working on a Matrix Visual that contains multiple KPIs for states.  The table needs to have the rank of the KPI against all other states.  For example:

User filter: North Carolina

	2022	2021	Rank
GSP	97.11%	79.92%	2 of 50
HPI	87.75%	63.97%	20 of 50
Unemployment	5.63%	54.47%	25 of 50
CPI	98.54%	29.38%	25 of 50

Users will also have the option to filter data by other areas such as MSA and CBSAs.  

Is there a better way to create the "Rank" column than to create a separate matrix, create a rankx column for all columns in my table, and then have that new matrix conditionally display based on the user filter?

Thanks

[v-henryk-mstf]

Hi @dallison ,

Can you provide the relevant test data and describe it so that I can answer it for you as soon as possible? Looking forward to your reply.

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,
Henry

[HoangHugo]

Hi,

you use only one formula for all KPIs? it is needed to use Matrix Visual

I think we use function Rankx():

rank = RANKX(SUMMARIZE(table,state column),formula KPI)

	2022		2021		Total
KPI	Rank	KP	Rank	KP	Rank
GSP	-	-	-	-	-	-
HPI	-	-	-	-	-	-
Unemployment	-	-	-	-	-	-
CPI	-	-	-	-	-	-

The Matrix seem like that if you add rank column