How to create a measure for rank change between two years

[MidtownMo]

Hi.  I have a table shown here where I used the RANKX function to create a measure to show the rank of a metric.  In this table I have calendar years going across the top.  Now I want to create another measure that would calculate the change in rank either between 2017 and 2021 or between 2018 and 2021.  Any suggestions?  Thanks in advance.

[v-chenwuz-msft · Accepted solution]

Hi @MidtownMo ,

I had to write three separate measures to return the rank of only 2017, 2018, and 2021, but I'm afraid that didn't fit into the matrix table, so I modified the visual column. Use a custom table or a manually entered table.

Measure = 
var _1 = RANKX(ALLSELECTED('Fact table'[ID]),CALCULATE(CALCULATE(SUM('Fact table'[Values]),FILTER('Fact table',format([Date],"yyyy")=SELECTEDVALUE(AxisTable[column])))))
var _2017 = RANKX(ALLSELECTED('Fact table'[ID]),calculate(CALCULATE(SUM('Fact table'[Values]),FILTER('Fact table',[Date].[Year]=2017))))
var _2018 = RANKX(ALLSELECTED('Fact table'[ID]),calculate(CALCULATE(SUM('Fact table'[Values]),FILTER('Fact table',[Date].[Year]=2018))))
var _2021 = RANKX(ALLSELECTED('Fact table'[ID]),calculate(CALCULATE(SUM('Fact table'[Values]),FILTER('Fact table',[Date].[Year]=2021))))
return
SWITCH(SELECTEDVALUE(AxisTable[column]),"2021-2017",_2021-_2017,"2021-2018",_2021-_2018,_1)

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[MidtownMo]

One follow up question:  I'd like the table to be sorted on either the 2021 column or the 2021-2017 column.  I tried clicking on the column headers to sort them but that appears to only work on a Total column (which is not in this table) or the ID column.  Is there a way to do this?  Thanks.

[MidtownMo]

Thank you.  This was very helpful.  One of the tricks is to create an Axis Table that treats the years as text and includes the "2021-2017" and "2021-2018" column names.  It took me a few minutes to realize that but your including a .pbix example was great.  Thanks again!

[MidtownMo]

I should have said in this matrix visualization, not table.