Forum Discussion
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
- Ashish_MathurSuper User
- DeermeatResolver I
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_MathurSuper User
You are welcome.
- DeermeatResolver I
Updated with sample file!
https://drive.google.com/file/d/1gnA49HvPpFjmR8fWNhHosAvtOiawq712/view?usp=sharing