Charts and Dashboards: Charting Example - Extended Case Study Part 3

Welcome back to our Charts and Dashboards blog series.  This week, we continue to take a look at an example of a chart, covering some of the intricacies behind the scenes with its workings.  This week, we consider custom number formatting further.

Last week, we looked at the formatting of the assumptions and calculations driving our chart:

But there’s another number formatting trick used in the workings.

Here, with ‘Gross Margin’ selected, the figures display as percentages – but what if we were to change this to a Metric that we don’t expect to display as a percentage?  Let’s use ‘Gross Profit’.

As you can see, (most of) the numbers aren’t displaying as percentages and that’s not because I’ve changed the formatting between screenshots.  We’ve used conditional custom number formatting.

This may be seen by Home -> Format -> Format Cells…(CTRL + 1):

If you recall the tables from last week, we can see the code for conditional formatting within the ‘Miscellaneous’ section.

Here we’ve used the following conditional formatting:

This means that if the number is less than one [1], then display it as a percentage to two decimal places, else display it as a whole number with a thousand separator if necessary.  However, this does have its limitations.  If any Metrics besides our Gross Margin contained a figure less than 1 (including negative values), it would display as a percentage.  This is why it’s important to know your data, so that you can understand if this is a viable solution.  In this case, Gross Margin is the only graphable variable that will generate values less than 1 in this line.  For those of you concerned about COGS, know that we have included the ABS function in this line to display COGS as a positive number, but more on that later.

That’s it for this week.  Come back next week for more Charts and Dashboards tips.