Can you create a reference line for each dimension?

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

Measure Swapping

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

Profit Ratio


Sales per Order

SUM([Sales])/COUNTD([Order ID])

Items per Order

SUM([Quantity])/COUNTD([Order ID])

A parameter is also required to allow the user selection. I chose to use a string parameter with the various measures displayed as below


Then to pull this altogether, I needed to build a calculated field to store the relevant value based on the parameter value selected

Display 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

  1. 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

WINDOW_AVG([Display Measure])

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!


One thought on “Can you create a reference line for each dimension?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s