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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AUaero
Responsive Resident
Responsive Resident

Unexpected Result With Matrix Conditional Formatting

Hi all, I'm getting an unexpected result when applying conditional formatting to a matrix visual.

Take a look at this formatted column.  The conditional formatting is set up as a gradient with the lowest value red, the highest value green, and the middle value white.

AUaero_0-1660845120329.png

 

Here's the column that I'm applying the conditional format to.  $85,694 is the median value for this set.

 

AUaero_1-1660845223632.png

Based on this rule, I would expect $85,694 to be white with all values above shading toward green and all values below shading toward red.  Instead Power BI is formatting $112,198 as the middle value.

Any ideas on this behavior?

Thanks!

1 ACCEPTED SOLUTION
v-rongtiep-msft
Community Support
Community Support

Hi @AUaero ,

According to the reply from the product group, Power BI implements the color gradient as a linear scale. You can set the Middle to a different value if you want, but by default Middle is the average of min/max.

They will take the idea from you into consideration for adding more Auto options to "middle".

 

Best Regards

Community Support Team _ Polly

 

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

View solution in original post

15 REPLIES 15
v-rongtiep-msft
Community Support
Community Support

Hi @AUaero ,

According to the reply from the product group, Power BI implements the color gradient as a linear scale. You can set the Middle to a different value if you want, but by default Middle is the average of min/max.

They will take the idea from you into consideration for adding more Auto options to "middle".

 

Best Regards

Community Support Team _ Polly

 

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

v-rongtiep-msft
Community Support
Community Support

Hi @AUaero ,

Have submited this issue internal to confirm(ICM: 329637339), would update here as soon as possible if there is any update about it.

 

Best Regards

Community Support Team _ Polly

 

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

lbendlin
Super User
Super User

It is likely caused by your nested measures.  The formatting works as expected for simple values

 

lbendlin_0-1661202028855.png

 

AUaero
Responsive Resident
Responsive Resident

@lbendlin I think you just got lucky with the set you tested with.  The average of the min and max ((7+1)/2) is 4, which is also happens to be the median of your set.  I tested using just a bunch of random numbers and get the same result as I do with the nested measures in the PBIX.  The median of this set is 48, but that isn't in the middle of the highlighted area.

AUaero_0-1661204105370.png

 

Ashish_Mathur
Super User
Super User

Hi,

What do you want to treat as the middle value - mean or median?  Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I would expect the middle value to the median, but I haven't found any documentation regarding conditional formatting that explains exactly what Power BI treats as the middle value.  Regardless, the value that is highlighted as the middle is neither the mean nor the median.

I can't share the full PBIX but I've created a stripped down version that replicates the problem.  As you can see, Power BI is treating the middle value as 103,391 while the median is 88,794 and the average is 92,752.

AUaero_0-1661197629130.png

I can't  share the the full PBIX due to confidentiality concerns, but here's a link to a simplified and sanitized version.

https://1drv.ms/u/s!AtHPCBKFQogGgsM-5tPOl07xWgWLjQ?e=hrveq0

Yeah, it's  a bit off.  Dashed is median, solid is mean/average.  Middle color is supposed to be white which is kinda absent.

 

lbendlin_0-1661200753982.png

You can raise an issue at https://issues.powerbi.com . If you have a Pro license you can consider raising a Pro ticket at https://powerbi.microsoft.com/en-us/support/pro/

It appears to be taking the max value of the list and subtracting the min value of the list. That number divided by two and then added to the min value to get the "middle value". 

nice theory but doesn't seem to hold

 

 

middle measure = 
var a = summarize('IAM Performance','IAM Performance'[IAMName],"s",[$ Sales / Day])
return 0.5*(maxx(a,[s])+minx(a,[s]))/17

Edit: never mind, didn't see the calendar filter.

 

Note:  (MIN+MAX)/2 is faster.

 

AUaero
Responsive Resident
Responsive Resident

I think you're onto something here.  Using your algorithm, I come up with 106,214 as the "middle" value.  This jives with with what Power BI is actually highlighting as the middle value.

Did you find this in the documentation somewhere or just figure it out?  Regardless, this seems like a dumb way to find the "middle".  This approach gives you average of the min and max values.  The middle of a set of numbers is the median.

AUaero_0-1661202367722.png

 

AUaero
Responsive Resident
Responsive Resident

Thanks for validating the problem - I suspected that this was a bug in the conditional formatting.  I've submitted an issue.

AUaero
Responsive Resident
Responsive Resident

Here's a link to the issue I've submitted. Based on @jgeddes findings I guess this isn't necessarily a bug, just a dumb way of resolving the middle value.  Please take a minute to go vote on this idea if you think the middle value should be the median instead of the average of the min and max values.

Gradient conditional formatting doesn't accurately... - Microsoft Power BI Community

A compromise would be to allow the developer to specify the rule that decides the "middle"  color.  

 

Worst case you would have to roll your own coloring rules.

lbendlin
Super User
Super User

The "Center" is most likely the average, not the median.

AUaero
Responsive Resident
Responsive Resident

The average of this set of number is 91,802.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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