Format fields in matrix based on dynamic condition

[Eric_Fabric]

Hello everyone

I am trying to conditionally format fields in a matrix, based on their value relative to the other values on the same row. To illustrate, I have created a sample matrix:

The report is for a divison (lets say division "A", represented in the first column) that needs to compare their performance to the other divisions (in the real scenario there are ten total). What is needed in the report is for the matrix to be conditionally formatted, so that the top three values for every row get highlighted, while the rest should not receive any coloring.

It is no issue, that only the lowest hierarchy level currently expanded gets highlighted.

In a second step it is required, that these top three values per row should also be used in a separate table to derive an average from them and then compare that value again to the (raw) value of division A.

I could not find a way that either of this works, as I didn't get how to implement a threshold for the conditional formatting that adapts to the values per row, which is what I thought might be the solution.
I am open to any recommendation on how to get this done, though I would prefer if no custom visuals had to be used, as this will cause a delay due to a mandatory internal approval process.
However I'd much rather have such a solution than no solution at all.

Thank you very much for any effort in helping me with this problem!

Kind regards,

Eric

[Anonymous · Accepted solution]

Hi @Eric_Fabric ,

Below is my table:

Please apply the following measures to your form background/font color.

Power BI's built-in conditional formatting settings do not directly support directly highlighting the first three values in each row of the matrix.

You can try to use the following dax conditional format, and replace the specific values with the data in your table

Color Measure = 
VAR _loadtime = SUM('Table'[LoadTime])
RETURN
SWITCH(TRUE(),
SELECTEDVALUE('Table'[Page]) = "Homepage" && _loadtime < 7.6, "Green",
SELECTEDVALUE('Table'[Page]) = "FlightSelect" && _loadtime< 15.36, "Green",
SELECTEDVALUE('Table'[Page]) = "Guest" && _loadtime< 14.53, "Green",
SELECTEDVALUE('Table'[Page]) = "Booking Summary" && _loadtime < 34.53 , "Green",
SELECTEDVALUE('Table'[Page]) = "Addons" && _loadtime < 24.43, "Green")

Measure are then created for the average

RankMeasure = RANKX(ALLSELECTED('Table'[Airline]), CALCULATE(SUM('Table'[LoadTime])), , DESC, Dense)




AvgTop3Measure = AVERAGEX(TOPN(3, ALL('Table'), [RankMeasure]), CALCULATE(SUM('Table'[LoadTime])))

The final output is shown in the following figure:

Best Regards,

Xianda Tang

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