Forum Discussion
Custom Colors in a Stacked Column Chart not displaying correctly
Additional information:
After changing the changing the Color Rule to use a number rather than a percentage, the column colors display properly.
I'm not sure if this is a bug or if I'm not understanding the Rule properly.Applying this rule (using Number), the graph displays as expected:
Applying this rule (using Percentage), the graph does not display as expected (colums with values > 65% show red:
I also came across a little tidbit, hidden away in the documentation:
Conditional table formatting in Power BI Desktop - Power BI | Microsoft Docs
The key takeaway from this document lies in this statement:Color by rules for percentages
If the field the formatting is based on contains percentages, write the numbers in the rules as decimals, which are the actual values; for example, ".25" instead of "25". Also, select Number instead of Percent for the number format. For example, "If value is greater than or equal to 0 Number and is less than .25 Number" returns values less than 25%.
And finally, I found another response from MS on this:The way the "Percent" conditional formatting works is different than one might think, it doesn't have anything to do with percent of total. It take the lowest and highest displayed amounts and uses those to determine the formatting bands based on the Percent you entered so your table is looking at these numbers to apply the formatting.
So, the answer appears to be that in order to have the colors displayed as a % of the displayed values, one needs to use the Rule for Number rather than Percentage. All in all, somewhat confusing in the designer as to what the two options represent.
Community SupportHi, jbryant
I hope this will help you understand the problem.
I have made an example to help understanding, see the following snapshot.
When formatting based on percentage, the reference percentage is the percentage of the difference between the minimum and maximum values.
Note that even though the data is showed as a percentage on the chart, the data is actually a decimal.
For example, in the example, if 100 is the minimum value and 200 is the maximum value, then the percentage reference is the difference between the maximum value and the minimum value, which is 100.
Note: The percentage based value is actually the percentage of the difference.
Check again the example in the documentation:
Note: If you use Percent instead of Number for fields containing percentages, you may get unexpected results. In the above example, in a range of percent values from 21.73% to 44.36%, 50% of that range is 33%. So use Number instead.
Min = 21.73% (0.2173),
Max = 44.36% (0.4436),
Diff = max-min = 0.2263 (22.63%)
50% of Diff = 0.11315 >> min + 50% of Diff =33%
In your case, since your data is displayed in percentage format and is actually a decimal, So you need to apply the conditional formatting on a number rather than a percentage approach.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.