Conditional formatting bug in advanced options

Status: Delivered
by mgcharumilind Regular Visitor on ‎01-08-2019 07:24 AM

There is a bug in the conditional formatting options when trying to use "advanced options" in a matrix which shows (distinct count) values as percents of row totals. When conditional formatting is first turned on, it looks as expected. Here you can see the largest values in each row are green, which makes sense.


However, as soon as I click into the "advanced controls" to adjust this formatting, Power BI switches the formatting to percent of grand total, even if I do not change anything in the advanced control settings. There is no option to switch it back. The only way to switch it back is to turn the conditional formatting off and back on again to reset it. Here is what this unexpected result looks like.

In the image below, I have switched my values to display the counts as percents of GRAND total so you can clearly see this is what the app is using in the above image.

This is problematic, and should be fixed! 


Status: Delivered
by Moderator v-yuezhe-msft
on ‎01-08-2019 09:49 PM


When you click on the Advanced control option to change formatting, you can find that the rule is based on sum of value, but not based on  percent values of row totals. Thus after you click "OK" rather than "Cancel" to close the formatting window, conditional formatting applies to a range of values in a single column of your original table, which results in a same effect as the second screentshot.

In your scenario, I would recommend you create a measure to calculate the percent of row totals as below, then apply conditional formatting based on this measure.

Measure = MAX(Table1[value])/CALCULATE(SUM(Table1[value]),ALLEXCEPT(Table1,Table1[Term Type]))



by Moderator v-yuezhe-msft
on ‎01-08-2019 09:50 PM
Status changed to: Delivered
by mgcharumilind Regular Visitor
on ‎01-09-2019 10:26 AM

Hi again, Lydia!


Thank you for the explanation - I see my guess was a little off.

I realize there is a workaround by which I can create a measure, but I still do not know why the conditional formatting defaults to highlight the largest percentages of either row or column totals, but then changes to only consider straight sums (or counts, in my case) if I try to adjust it, and cannot be changed back. It starts out with the expected/desired behavior, but then changes and does not go back without resetting it.

This means users cannot adjust conditional formatting at all if they want to use percent of row or column total in the matrix visualization, unless they go through the DAX workaround you mentioned. This seems like an issue to me, but I guess the product team disagrees?

Thanks again,

by Moderator v-yuezhe-msft
on ‎01-10-2019 01:53 AM


I will report this issue internally and post back once I  get any updates.