For Luke’s first challenge of 2023, he asked us to recreate this visualisation which showed multiple chart types (donut charts and bar charts) within a tabular layout, all built on a single sheet.

As with most challenges, I’m going to first work on building out the calculations needed to provide the data being presented.

Building the calculations

Firstly we need to identify the number of orders, based on unique Order IDs

Total Orders

COUNTD([Order ID])

and then to get the average order value we need

Avg Order Size

SUM([Sales]) / [Total Orders]

format this to $ with 0 dp.

In order to build the donut chart, we will need two measures for each region; one which shows the % number of orders for that region, and one which shows the % number of orders across the remaining regions.

% Orders for Region

[Total Orders]/SUM({FIXED:COUNTD([Order ID])})

Format this to % with 0 dp

and then

% Orders for not Region

1 – [% Orders For Region]

format this to % with 0 dp.

Pop all these out in a table along with Sales formatted to $ with 0 dp, and Sort the Region field based on Total Orders descending.

Building the table view

Add Region to Rows and sort by Total Orders descending.

All the columns are going to be managed by what I refer to as a ‘fake axis’. Double click into the Columns shelf and type in MIN(0). This will create an axis.

Change the mark type to Text and add Total Orders to the Text shelf. Remove all the text from the Tooltip dialog box.

This is our 1st ‘column’ in the table.

Create another column by adding another instance of MIN(0) to Columns.

Change the mark type of this 2nd MIN(0) marks card to Pie Chart and remove Total Orders from the text shelf.

Add Measure Names to the Filters shelf, and ensure only % Orders for Region and % Orders for not Region are selected.

Add Measure Values to the Angle shelf of the pie chart, and add Measure Names to Colour. Adjust the colours to suit and ensure a white border is added (via the Colour shelf options). Again remove all the text in the Tooltip dialog.

Now add another instance of MIN(0) to Columns.

Change the mark type of this marks card to Circle and set the colour to white. Add % Orders for Region to the Label and align centre.

Now make this Min(0) pill to be dual axis and synchronise the axis. Reduce the size of the circle mark type marks card, so some of the pie chart is visible. Remove Tooltip text again.

Add another instance of MIN(0) for our ‘3rd’ column. Set the mark type to Text and add Sales to the Text shelf, and remove the tooltip text.

For the final column, add Avg Order Size to Columns. Change the mark type to bar. Reduce the size and adjust the Colour to suit. Add Avg Order Size to the Label and remove all tooltip text.

Remove all gridlines zero lines and column dividers. Add row dividers at the highest level so you have a divider per row. Uncheck Show Header on the first MIN(0) pill to hide the axes, and right click the Region column label and Hide field labels for rows.

This gives you the viz you need to add to a dashboard. Use a horizontal container with a blank and 4 text boxes positioned above the viz to provide the label heading to the columns.

OR…

you can do what I did, and having not noticed the comment about the textboxes, used further instances of MIN(0) to make every ‘column’ a dual axis so I could use the axis label for the secondary axis displayed at the top to provide the required labels.

I set the mark type of these additional MIN(0) axis to polygon, and ensured no other pills existed on the marks shelf. The tick marks on all the top and bottom axes had to be set to none, and the title on all the bottom axis had to be deleted.

The benefit of using the text boxes option is that you aren’t left with a ‘blank’ space underneath the chart. However wide your axis is at the top to display the labels, will be repeated as ‘blank space’ underneath the chart too (vote up this idea to prevent this).

My published viz is here.

Happy vizzin’!

Donna