Forum Discussion

Deermeat's avatar
Deermeat
Resolver I
4 years ago
Solved

Ranking Values in a Measure

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!

4 Replies