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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Peppearson
Helper I
Helper I

Question Re Math Behind Conditional Formatting Rules

Hi 
I'm trying to understand the maths behind the cell elements condtinal formatting rules see below

This has colour codes my values in the following way:

509 - Green

397 - Green

353 - Yellow

250 - Yellow

94 - Red

80 Red

 

Note - The field is a generic sales measure


Can anyone clarify the maths that leads to this result?

Conditional Formatting Rules.png

Many thanks,

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @Peppearson 

My understanding is that percentages (in this dialog box) correspond to linear interpolations between the Min and Max values.

So the Value corresponding to a given percentage p is Min + p/100 * (Max - Min).

 

To put it the calculation in steps, using your figures as an example:

  1. Take Min = 80
  2. Take Max = 509
  3. Calculate Range = Max - Min = 509 - 80 = 429
  4. Then for any percentage p, the value V is:
    V = 80 + p/100 * 429
  5. So your values for each percentage are:
    1. V(0%) = 80 + 0/100 * 429 = 80
    2. V(33%) = 80 + 33/100 * 429 = 221.57
    3. V(67%) = 80 + 67/100 * 429 = 367.43
    4. V(100%) = 80 + 100/100 * 429 = 509

The icons are then explained based on where the values fall relative to these threshold values.

 

It might have been more intuitive if there was an option for percentiles, so that the smallest 33% of values could be given a certain format, and so on. You could still achieve this, but would need to write a measure to handle it.

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
Peppearson
Helper I
Helper I

Thanks for the explanation! Makes sense now.

OwenAuger
Super User
Super User

Hi @Peppearson 

My understanding is that percentages (in this dialog box) correspond to linear interpolations between the Min and Max values.

So the Value corresponding to a given percentage p is Min + p/100 * (Max - Min).

 

To put it the calculation in steps, using your figures as an example:

  1. Take Min = 80
  2. Take Max = 509
  3. Calculate Range = Max - Min = 509 - 80 = 429
  4. Then for any percentage p, the value V is:
    V = 80 + p/100 * 429
  5. So your values for each percentage are:
    1. V(0%) = 80 + 0/100 * 429 = 80
    2. V(33%) = 80 + 33/100 * 429 = 221.57
    3. V(67%) = 80 + 67/100 * 429 = 367.43
    4. V(100%) = 80 + 100/100 * 429 = 509

The icons are then explained based on where the values fall relative to these threshold values.

 

It might have been more intuitive if there was an option for percentiles, so that the smallest 33% of values could be given a certain format, and so on. You could still achieve this, but would need to write a measure to handle it.

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.