Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Hi,
You may refer to my solution in this file.
Hope this helps.
Hi,
You may refer to my solution in this file.
Hope this helps.
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.
Updated with sample file!
https://drive.google.com/file/d/1gnA49HvPpFjmR8fWNhHosAvtOiawq712/view?usp=sharing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |