cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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?

Many thanks,

1 ACCEPTED SOLUTION
Super User

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
Blog
2 REPLIES 2
Helper I

Thanks for the explanation! Makes sense now.

Super User

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
Blog

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors