Background color variation in conditional formatting of cells other than Excel

[Anonymous]

Hello everyone

I'm trying to get a table in PowerBI to format the background color in a table based on the sum of values. It works, but the color variation looks very differentnet than in the same table in Excel. The Excel table has a much wider range of yellow and orange colors "between", while the PowerBI is almost entirley green or red. However, it should work the same way because the scale on both is red-yellow-green. Any idea how to change this?

Thank you!

[MFelix · Accepted solution]

Hi @Anonymous ,

As in the Excel rule you need to fill in the middle value with the percentile 50% and on PBI this is not calculated with percentile however you can make a workaround:

• Create a measure to calculate the percentile:

percentile = CALCULATE(PERCENTILE.EXC('Table'[Value];0,5);ALL('Table'))

• If you are using a measure instead of a column on your data you need to do a aggregator function:

percentile = 
PERCENTILEX.EXC(ALL('Table');[Measure 2];0,5)

As you can see the difference is where the ALL syntax is placed.

• Create a measure to calculate the values above and below the percentile value

Percentile_Variation = SUM('Table'[Value])-[percentile]

If you are using a measure replace the SUM by the name of your measure

• Now just make your condittional formating based on the Percentile variation and set the middle value to 0

The thought process is  that the middle value (yellow) should be place in the percentile value so for that the value is 0 because I'm picking up that value and substrating from the original value so them the diverging colours work correctly.

You can change the percentile value on the measure to adjust to your needs if you don't want 0,5.

See PBIX file attach. (added the category so that alll the values would appear in the table.