cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Deermeat
Resolver I
Resolver I

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:

Untitled.png

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])

 

Untitled.png

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!

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may refer to my solution in this file.

Hope this helps.

Untitled.jpg


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

You may refer to my solution in this file.

Hope this helps.

Untitled.jpg


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Deermeat
Resolver I
Resolver I

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors