Ranking Values in a Measure

[Deermeat]

Hello,

I have two measures that I used to calculate the average of Score and YTM over different industries:

YTM (Mid) = 
AVERAGEX(
	KEEPFILTERS(VALUES('Data (All Issues)'[Industry])),
	CALCULATE(AVERAGE('Data (All Issues)'[YTMMid]))
)

Score = 
AVERAGEX(
	KEEPFILTERS(VALUES('Data (All Issues)'[Industry])),
	CALCULATE(AVERAGE('Data (All Issues)'[Score]))
)

I am trying to rank each of the industry based on the value of multiplying Score with YTM. I want to be able to see historical values as well. The visual should look like this:

In each column, they are all ranked from the highest value to the lowest value based on this measure:

ScoreYTM = [Score] * [YTM (Mid)]

Since they need to allow slicers to edit the different data points allowed into the average Score and YTM calcuation, I am not able to use calculated columns. It needs to remain in a measure and affected by the slicers.

I tried using RANKX to get the ranking of the different industries, however I was not able to swap industry and ranking to achieve the result I wanted. The RANKX measure refuses to go into the Rows section of the matrix:

Ranking = RANKX(ALL('Data (All Issues)'[Industry]), [ScoreYTM])

Does anybody know how I can create a measure to swap the ranking and industry, so that I can achieve the desired result?

I have attached a sample file: https://drive.google.com/file/d/1gnA49HvPpFjmR8fWNhHosAvtOiawq712/view?usp=sharing

Thank you!

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution in this file.

Hope this helps.

[Deermeat]

Hi @Ashish_Mathur,

Thank you so much. That has indeed helped me to solve this problem.

For anyone curious, Ashish used this code:

ActualRank = CALCULATE(MAX('Data (All Issues)'[Industry]),FILTER(ADDCOLUMNS(VALUES('Data (All Issues)'[Industry]),"ABCD",CALCULATE([Ranking],CALCULATETABLE(VALUES('Data (All Issues)'[Industry])))),COUNTROWS(FILTER('Rank',[abcd]='Rank'[Rank]))>0))

With a new rank table that just has the numbers of industry in it for the rows.

Once again, thank you Ashish!

[Ashish_Mathur]

You are welcome.

[Deermeat]

Updated with sample file!

https://drive.google.com/file/d/1gnA49HvPpFjmR8fWNhHosAvtOiawq712/view?usp=sharing