How to use Icon Sets in Power BI and Excel
Wyn Hopkins
Leading a team that solves data challenges for companies and people Power BI | Excel | Fabric | Solver Budgeting & Consolidation. Control costs, reduce risk and assist growth Microsoft MVP ??5 Million+ YouTube views
I find the default Icon settings confusing in both Excel and Power BI.
In July 2019 conditional formatting Icon Sets were added to Power BI and it caused me to revisit how they work in Excel. And I'll be honest, I realised I didn't really ever understand how the default settings were working
In Excel you would highlight a range of numbers and choose Conditional Formatting : Icon sets. But you'd get odd results such as in the screenshot where negative numbers have up arrows
This happens because the default option is set with the values split into 3 groups of the lowest 33%, middle 33% and upper 33%
The Maths (for those number junkies out there)
To see how the calculation actually works, take a look at this example (thanks to fellow MVP Tim Heng for setting me straight on this)
So using the Min and Max of the selected values (A5:A8) the Total Difference between this Min and Max is calculated (e.g. 9 in the left hand side example)
Then the difference between the value being considered (e.g. cell A7 contains 4) and the Min (1) is calculated giving 3 and finally that is expressed as a % of the total difference = 3/9 = 33.33% which sneeks over the 33% threshold and the icon in A7 turns yellow.
In the right hand image the max number in E5:E8 is 11 therefore the difference is 10. Then looking at a particular value say E7 we have the number 4. So 4 minus the min of 1 is 3 and then 3 /10 = 30% which falls bellow the 33% threshold and the icon is Red.
Personally I don't find this useful and I've never left icon sets with these default definitions
I've always clicked on Manage Rules and changed the rules to something I control more directly by switching the Type (down the bottom right of the image) to Number instead of Percent
Power BI
A similar methodology is required for Power BI
You are then presented with this screen
In order to apply simple negative / positive formatting you should set it up as follows:
I find the options presented in Excel and Power BI confusing so I hope this helpful to others in my situation.
Wyn
Policy Manager
5 年Thanks for this. I came here looking for tips on how to conditionally format a set of numbers *relative to* another set of numbers. So e.g. if cell A1 is $2m, more than cell B1 at $1m, A1 shows a green disc. This is very basic visual RAG reporting stuff, but doesn't seem to be readily catered for. Seems like a huge miss.
Vice President Technical Solutions @ Veson Nautical | Maritime Tech
5 年This was exactly what I needed and your write up was perfect. Thanks.