Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Background color variation in conditional formatting of cells other than Excel

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!

שאלה לpowerBi.jpg

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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

conditional.gi.png

 

 

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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

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

conditional.gi.png

 

 

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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.