Help Needed: Ranking Values and Applying Conditional Formatting in Matrix Visual

[ChrisPBIUser]

I’m working on a matrix visual where the metrics are in the rows and the weeks are in the columns. The value field is set to display as a percentage of the column total.

I need to rank the values in each cell based on specific criteria, assigning ranks of 1, 2, and 3. After assigning these ranks, I want to apply RAG (Red, Amber, Green) conditional formatting based on these ranks.

I’ve tried several methods to achieve this but haven’t had any success. Any guidance or assistance on how to create these ranks and apply the conditional formatting would be greatly appreciated.

So I'm looking for this

Thank you!

TermExt Rank =
VAR CurrentWeek = SELECTEDVALUE('Weekly PBI Data'[Week Commencing])
VAR TotalValue = CALCULATE(SUM('Weekly PBI Data'[Value]), ALL('Weekly PBI Data'))
VAR WeekValue = CALCULATE(SUM('Weekly PBI Data'[Value]), 'Weekly PBI Data'[Week Commencing] = CurrentWeek)
VAR ValuePercentage = DIVIDE(WeekValue, TotalValue, 0)
VAR Metric = SELECTEDVALUE('Weekly PBI Data'[Metric])

RETURN
SWITCH(
TRUE(),
Metric = "1" && ValuePercentage <= 0.40, 1,
Metric = "1" && ValuePercentage > 0.40 && ValuePercentage < 0.50, 2,
Metric = "1" && ValuePercentage >= 0.50, 3,

Metric = "2" && ValuePercentage >= 0.40, 1,
Metric = "2" && ValuePercentage > 0.33 && ValuePercentage < 0.40, 2,
Metric = "2" && ValuePercentage <= 0.33, 3,

Metric = "3" && ValuePercentage >= 0.20, 1,
Metric = "3" && ValuePercentage > 0.15 && ValuePercentage < 0.20, 2,
Metric = "3" && ValuePercentage <= 0.15, 3,

Metric = "4" && ValuePercentage >= 0.125, 1,
Metric = "4" && ValuePercentage > 0.10 && ValuePercentage < 0.125, 2,
Metric = "4" && ValuePercentage <= 0.10, 3,
BLANK()
)

[ChrisPBIUser]

Morning, thanks for getting back.  Unfornately the PBIX file contains sensitive commercial data so I've taken a snapshot of sample data which shows week commencing, the metric which goes up to 5 and a value.  The remaining 4 metrics will need to have the same week commencing as per metric 1.

So week commencing 1 

Matrix visual

The for the value

[ChrisPBIUser]

Hi, thanks for the reply but it still gives out the same results that my original post gave.

As you can see the first value is 49% (0.49) so according to the logic the rank colour and rank value show Red and 1 where as the value is between 0.40 and 0.50 so it should return Amber and 2
The second value for 08/01/2024 is 50% (0.50) so this should be Green and 3

[Anonymous]

Hi @ChrisPBIUser 

It seems to me that there is nothing wrong with these measures.

I tried to reproduce your situation, but I found that I didn't have your specific data.
If you can, you can upload a .pbix file or provide a screenshot of it.

You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @ChrisPBIUser - First lets create a measure that assigns colors based on the rank as below

RankColor =
SWITCH(
TRUE(),
[TermExt Rank] = 1, "Red",
[TermExt Rank] = 2, "Amber",
[TermExt Rank] = 3, "Green",
BLANK()
)

Now the rank measure create it to calculate accurately get the values

TermExt Rank =
VAR CurrentWeek = SELECTEDVALUE('Weekly PBI Data'[Week Commencing])
VAR TotalValue = CALCULATE(SUM('Weekly PBI Data'[Value]), ALL('Weekly PBI Data'))
VAR WeekValue = CALCULATE(SUM('Weekly PBI Data'[Value]), 'Weekly PBI Data'[Week Commencing] = CurrentWeek)
VAR ValuePercentage = DIVIDE(WeekValue, TotalValue, 0)
VAR Metric = SELECTEDVALUE('Weekly PBI Data'[Metric])
RETURN
SWITCH(
TRUE(),
Metric = "1" && ValuePercentage <= 0.40, 1,
Metric = "1" && ValuePercentage > 0.40 && ValuePercentage < 0.50, 2,
Metric = "1" && ValuePercentage >= 0.50, 3,
Metric = "2" && ValuePercentage >= 0.40, 1,
Metric = "2" && ValuePercentage > 0.33 && ValuePercentage < 0.40, 2,
Metric = "2" && ValuePercentage <= 0.33, 3,
Metric = "3" && ValuePercentage >= 0.20, 1,
Metric = "3" && ValuePercentage > 0.15 && ValuePercentage < 0.20, 2,
Metric = "3" && ValuePercentage <= 0.15, 3,
Metric = "4" && ValuePercentage >= 0.125, 1,
Metric = "4" && ValuePercentage > 0.10 && ValuePercentage < 0.125, 2,
Metric = "4" && ValuePercentage <= 0.10, 3,
BLANK()
)

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!