Ann Jackson returned this week with a challenge primarily focussed on formatting.
The core requirement this week was to be able to present different measures on a chart, based on a user selection, but where the values displayed were of differing numerical formats
- Sales per order in $ to 0 decimal places, formatted to show a ‘,’ every 1,000.
- Profit Ratio as a % to 1 decimal place
- Items per order as a numerical value to 2 decimal places
My focus points this week are
- Measure swapping
- Adding the line labels
- Labelling the y-axis
- Adding the reference lines
- Building the blocks
This is technique that should be in everyone’s arsenal, as it’s a great way to present multiple views of the data without the need for multiple instances of the chart – it saves space and clutter but continues to allow flexibility.
The 3 measures required needed to be defined through calculated fields
Sales per Order
Items per Order
A parameter is also required to allow the user selection. I chose to use a string parameter with the various measures displayed as below
SELECT A MEASURE
Then to pull this altogether, I needed to build a calculated field to store the relevant value based on the parameter value selected
CASE [SELECT A MEASURE]
WHEN ‘Profit Ratio’ THEN ROUND([Profit Ratio]*100,1)
WHEN ‘Sales Per Order’ THEN ROUND([Sales Per Order],0)
WHEN ‘Items Per Order’ THEN ROUND([Items Per Order],2)
It’s within this field I chose to define the number formatting I wanted to display, and by then setting the number format of the field to Number Standard, it seemed to show what I intended on hover. Add Display Measure to Rows and plot against QUARTER(Order Date) coloured by Category to get the display below.
Adding the line labels
However while the numeric format is what’s required, I haven’t got the $ or % symbol, and I can’t apply that as part of the default formatting.
Instead I created explicit prefix & suffix fields
$ Label Prefix
IF [SELECT A MEASURE] = ‘Sales Per Order’ THEN ‘$’ END
% Label Suffix
IF [SELECT A MEASURE] = ‘Profit Ratio’ THEN ‘%’ END
Adding these 2 fields to the Detail shelf, they can then be referenced in both the Tooltip and the Label as follows
<$ Label Prefix><AGG(Display Measure)><% Label Suffix>
Based on the logic, either both fields will be NULL/blank else, only one will be populated, so you’ll never get $1,000% displayed!
Labelling the y-axis
Amend the y-axis to delete the Display Measure title, then add the SELECT A MEASURE parameter to the Rows shelf. Rotate and format accordingly.
Adding the Reference Lines
Adding a reference line – simples, surely! But why would Ann be making a challenge if it was that easy….hmmmmm! So what were the challenges posed here
- If you add an ‘Average’ reference line, you don’t get a value per line (even if you select the ‘per cell’ option) – you just get one average line. If the chart was split so there was a row per category, you’d be able to get this.
2.The lines displayed can’t be created via a ‘dual’ axis chart where the 2nd axis is showing the average, because the line format is a finely dotted line, and we can’t format a line mark this way. Proper reference lines can be formatted though, so I concluded the lines had to be true reference lines.
3. However, the labelling of a reference line is quite limited, and while I can show the value, I can’t use other calculated fields (ie the Prefix/Suffix fields) on the reference line label…
I came up with the following solution : create separate fields to store the AVG values for each Category, so that I could add 3 separate reference lines to the main chart; then create a dual axis line chart which also showed the average per category, label the line accordingly, and reduce the opacity of the line to 0%.
Ref Line Per Category
Stores the average of the data displayed, and can be varied based on the table calc settings.
Ref Line – Tech
IF MIN([Category]) = ‘Technology’ THEN [Ref Line per Category] END
Only stores the average for the Technology data. I created equivalent ones of these for Ref Line – Office and Ref Line – Furniture
All 3 fields were added to the Detail shelf, then added as 3 different reference lines, coloured and formatted as a dotted line accordingly
To make the labels, I added Ref Line per Category to the Rows shelf to create a secondary axis. The table calculation was set as below
This produces a straight line for each category on a second chart, which I duly labelled by choosing to label the start of line, and aligning top left
I then set the opacity of the line colour to 0%, which makes the line disappear
I then set the chart to be dual axis, and synchronised the axis.
Building the blocks
For the block chart, I started by building a Tree Map (using Show Me) based on Category and the Ref Line Per Category fields.
I created a Rank field as
RANK([Ref Line per Category])
which I added to the Tooltip. I also then filtered the chart to Rank=1 to give me the main block. I then duplicated this sheet, and changed the rank filter to Rank=2, and repeated again for Rank=3. This gave me 3 sheets I could then organise onto the dashboard, as just using the tree map view directly, I couldn’t control how the different sections would display.
This was a fun challenge this week, slightly less taking than the previous weeks! My published viz is here.
Happy vizzin’! Stay Safe!