This week in DS24 we have been learning to recreate dashboards effectively using containers. One key element of every dashboard - which is a crucial metric for all organisations - is the Key Performance Indicator (KPI). Providing these insights in your dashboard in big text is one of the most helpful and clear ways to do so because it allows the reader to quickly evaluate the successes of the company. These big text KPIs are called BANs (Big Ass Numbers). Below are two examples (using Sample - Superstore data) showing how to improve the format of your BAN for use in a dashboard.
Simple BAN text
The first example is the simplest and is used in the title in the above example. It shows the sum of sales from the most recent year with the percentage change from last year. This is really easy for the reader to interpret and gives context via a direct comparison to the previous year (PY).
A couple of tips to keep in mind when creating this sort of BAN:
- Make the text you want the reader to see first - or the most important metric - bold. In this case it is the sum of sales (£733,215).
- When creating a comparison to another measure for context, it is always good to include a simple graphic to show positive or negative change. In this case a '+' and '-' sign are used, but '▲' and '▼' are clear and simple (which can be accessed through the ASCII keyboard). To change how this number format for the percent change is displayed, right click on the value -> Default Properties -> Number Format and configure as follows:
Coloured BAN to show positive or negative change
This is a dynamic BAN used in the title for the worksheet: the positive change is coloured grey and the negative change is coloured red to clearly show a negative trend. The steps to create this sort of dynamic title are as follows:
- Create two calculated fields which will be used to colour the text:
The second calculated field is identical apart from the operator being '>' instead of '<'.
2. Drag these two fields onto the details card so that they can be used in the title.
3. Format the title as follows, using both calculated fields and colouring them red for the less than zero field and grey for the more than zero field.
4. Change the Number Format for both calculated fields, adding a third semi-colon into the custom field and adding a space after that semi-colon. This will ensure that if the field equates to zero then it will not show in the title:
5. Add a filter (or a parameter action etc.) to the worksheet so the user can change the values of the title to check whether the title colours change correctly. In the above gif, Region is added to the filters to display a negative change in sales in the central region.
You may have noticed there is one case where the actual change in sales from the previous year could equal zero, in which case both calculated fields will not display in the title. This can be fixed with another calculated field and displaying zero using the Default Number Format.