For this week’s challenge we’re using the Superstore data set to simple averages with weighted averages
Connect to the data and on a new sheet add Order Date to Filter and select the last 3 months in your data set (I had the 2025.3 instance of Superstore, so the data was filtered to Oct, Nov & Dec 2025.
Simple Average
In the data we have, a single Order ID can have multiple order lines (ie Products), and the quantity of each product can differ. Different discounts can be applied to each product. In the example below, we are at the lowest ‘grain’ of the data, ie each row in the table is equivalent to a single row in the raw data set. As a result the SUMs and the AVG values are identical. This order has 5 order lines. (Note, the Discount field has been formatted to a % with 1 dp).
If this data is summarised just at the Order ID level, the SUM and AVG differ, as you would expect.
The AVG([Discount]) is automatically being computed by Tableau as SUM(Discount) / number of rows associated to the Order ID, which is 280/5 = 56%.
This is the ‘simple’ average and is taking no account of the Quantity associated to each order line.
Weighted Average
We need to get the average based on the fact that in this example, there are actually 26 ‘items’ ordered across the 5 order lines, so we need the following
At the raw line level, the Discount is being multiplied by the Quantity (so a weighting is being applied per line as the quantities and discounts can vary). This is then being aggregated (ie summed up) and then the average is determined by dividing by the total number of items ordered. So based on the example we have (2*0.2) + (3*0.8) + (5*0.8) + (9*0.8) + (7*0.2) = 15.4 which is then divided by 26 = 0.592 or 59.2%
Building the Viz
Add Discount to Columns and change the aggregation to Average. Then add Quantity to Rows and change that aggregation to Average too. Add Order ID to Detail. Change the mark type to circle.
Create a new field
Difference from correct metric
ABS([Weighted Avg] – AVG([Discount]))
Add to Tooltip and then create
Is difference?
ROUND([Difference from correct metric],3)<>0
note – I am rounding here as I was getting some anomalies to Erica’s solution due to very very small differences
Add to Colour and adjust to suit. Reduce the opacity and size as desired. Add Discount toLabel (aggregated to Average). Set the Label to display only when highlighted.
Add Difference from correct metric to Filter
Remove row/column dividers and adjust Tooltip as required.
Name the viz Simple Avg or similar and then duplicate the sheet. Replace the AVG(Discount) pills on the Label and Columns shelf with the Weighted Avg pill. Adjust tooltip and rename axis title. Name this sheet Weighted Avg or similar.
Creating the Viz in Tooltip
On a new sheet, add Order ID, Category to Rows and then Quantity and Discount into the table aggregated at the Average level. Remove row banding and column dividers and adjust row dividers. Name the sheet Order Details or similar.
On the Simple Avg sheet, edit the Tooltip and insert the Order Details sheet, adjusting the width if required.
Repeat on the Weight Avg sheet.
Creating the Dashboard
Using layout containers, create the desired arrangement. My hierarchy is below
I used blank objects with a grey background to create the horizontal and vertical ‘lines’, using outer padding to help. Here’s an example of the top horizontal line. The height of the object is 134 pixels, but I’ve added 130 px top padding, making the ‘line’ 4px wide.
Add a highlight dashboard action that applies when a mark is selected
And that should then be done! My published viz is here.
Yoshi chose to challenge us this week to try using the multi-fact relationship model, something I hadn’t used before so I was keen to have a go.
I have to admit, I did ultimately find the challenge a bit tricky… I did have to look at the hint in the challenge to see how Yoshi had folded in the additional month data source, and then when building the vizzes, I was getting slightly different numbers on occasion. I wasn’t sure whether this was due to differences in how I’d applied the actual relationship fields between the tables in the model, but can’t actually get to Yoshi’s data model in the solution to tell. I used the information on the help article to determine the linking fields, so I think it’s right…
I also struggled with the final requirement – to always show all the libraries in the display, even when there were no copies of the selected book. As soon as I restricted the display to just show the data for the selected book, the libraries which didn’t stock the book disappeared, which with relationships is what I expected as they act like ‘inner joins’. Even looking at Yoshi’s solution, I can’t see what magic trick has been applied, unless there’s something in the model I haven’t applied..
So I’ll explain what I did, and if you can figure out where I may have gone wrong, or if you concur with my set up, then please let me know 🙂
Modelling the data
Download the 3 data sources from Yoshi’s link.
Connect to the Bookshop excel file. Drag Checkouts onto the canvas first – this is one of the base tables.
Drag Book onto the canvas and verify the tables are related by the Book ID field from each table.
Drag Author onto the canvas after Book and verify the tables are related on the Auth ID fields.
Drag Award onto the canvas after Book and verify the tables are related on the Title fields.
Drag Edition onto the canvas after Book and verify the tables are related on Book ID
Drag Info onto the canvas after Book. This time you’ll need to join Book ID from Book to a relationship calculation from Info of [BookID1]+[BookID2]
Drag Ratings onto the canvas after Book and verify the tables are related on Book ID
Add a new connection to the BookshopLibraries excel file and then drag in Catalog after Edition and verify the tables are related on ISBN
Drag in Library Profile after Catalog and verify the tables are related on Library ID
Next from the Bookshop excel data source, add Sales Q1 onto the canvas and drop when the New BaseTable option appears
Multi select the Sales Q2, Sales Q3 and Sales Q4 tables then drag onto the canvas and drop onto the Sales Q1 table when the Union option appears
Right click on the Sales Q1 unioned object and rename to Sales. Add a relationship from Sales to Edition verifying the tables are related on ISBN.
Add a new connection to the Months csv file, then add the Month.csv object onto the canvas so it is related to the Sales table. This time you’ll need to create a relationship calculation on Sales as DATEPART(‘month’, [Sale Date]) and join to Month
Finally also add a relationship from Checkouts to Month.csv, creating a relationship on Checkout Month to Month.
Now we have a model, we can start building the vizzes.
Building the scatter plot
To start, to verify I’m getting the data I expect, I’ll build out a table
Add BookID (Book) and Title to Rows. We need to indicate if the book has won an award.
Awarded?
COUNT([Award])>0
Add this to Rows.
We will be plotting the average number of checkouts per month against the average number of orders per month., so create
Avg Checkouts per Month
SUM([Number of Checkouts])/COUNTD([Checkout Month])
Then create
Avg Orders per Month
COUNT([Order ID])/COUNTD(MONTH([Sale Date]))
Add into the table. The number won’t be correct though, as we are displaying data from the Checkouts base table and the Sales base table without including any field from any tables that link them. To resolve this add BookID (Edition) onto Rows, and if you spot check some of the numbers against the scatter plot solution, they should match (or be very close). – figures for Portmeirion matched for me.
We also need to highlight which book is selected, so create a parameter
pSelectedBookID
string parameter defaulted to <emptystring>
Then create field
Is Selected Book
[BookID (Edition)] = [pSelectedBookID]
Show the parameter and enter the string PP866 (for Portmeirion). Add Is Selected Book to Rows to verify true is displayed against this row.
On a new sheet, add Avg Checkouts per Month to Columns and Average Orders per Month to Rows. Add Book ID (Edition) to Detail. Add Awarded to Shape and adjust shapes as required. Add Awarded to Size and adjust. Add Is Selected Book to Colour and adjust to suit. Make sure True is listed before False for the Colour legend to ensure the selected book mark is always ‘on top’. Hide the null indicator
Create a new field
Label: Selected Title
IF [Is Selected Book] THEN [Title] END
Add this to Label, adjust the font style, match mark colour and align top centre. Adjust the tooltip. Update the title and name the sheet Scatter or similar.
Building the Ratings Bar
Add Rating as a discrete dimension (blue disaggregated field) to Rows. Add Is Selected Book to Columns and add Ratings (Count) to Text. Exclude the Null column that appears (Filter for Is Selected Book). Add a percent of totalquick table calculation to CNT(Ratings) and adjust to compute using Rating.
Duplicate the sheet. Move CNT(Ratings) to Columns and Is Selected Book to Colour. Adjust colours to suit. Unstack the marks (Analysis menu > stack marks off). Add Is Selected Book to Size. Adjust so that True is listed first on the size legend, so it is both narrower than false, and will also now be ‘in front’. Update the tooltip and hide the Rating label heading (right click > hide field labels for rows).
Create a new field
Selected Book Ratings
IF [Is Selected Book] THEN {FIXED [Title]: COUNT([Ratings])} END
and add to Detail. Then update the title of the sheet adding a reference to this field. Exclude the Null option that now appears (Rating is added to the Filter shelf). Name the sheet Ratings Bar or similar.
Build the trend line
We need to show the average number of books ordered and the average number of books checked out each month. For this we need
Avg Orders per Book
COUNTD([Order ID])/COUNTD([BookID (Edition)])
and
Avg Checkouts per Book
SUM([Number of Checkouts])/COUNTD([BookID (Edition)])
On a new sheet, add Month (from Months.csv) to Rows as a discrete dimension (blue pill) and add Avg Checkouts per Book and Avg Orders per Book into the table text. Then add Is Selected Book to Columns.
This is where some of my numbers don’t align with the values in Yoshi’s solution, but it’s not clear why… Exclude the Null columns (Is Selected Book added to Filter).
We’re going to want to display the month numbers as the month names, so create a new field to create a ‘date’ out of the number
Month Name
//convert to a date, then use month/formatting functions in display MAKEDATE(2000, [Month],1)
On a new sheet add Month Name to Columns as a continuous pill at the month (May) level (green pill). Add Avg Orders per Book and Avg Checkouts per Book to the Rows and add Is Selected Book to the Colour shelf of the All marks card. Adjust colour if needed, and ensure True is listed first (on top).
Adjust the y-axis titles, and update tooltip if required. Remove the title from the x-axis. Format the x-axis so the scale uses abbreviated month names
Add Is Selected Book to Filter and exclude Null. Update the sheet title, Name the sheet Monthly Trend or similar.
Building the Library Bar
Add Library to Rows and Number of Copies to Columns. Add Is Selected Book to Filter and set to True. Show mark labels, and adjust to match mark colour. Hide the x-axis. Hide the Library row header. Apply row banding so every other row is shaded. Update the tooltip and sheet title. Name the sheet Library Bar or similar.
Creating the title sheet
On a new sheet add Is Selected Book to Filter and set to True. Then add Title, First Name, Last Name and Genre to Text. Set the mark type to shape and select a transparent shape (see here for info on how to set this up). Set the sheet to Entire View. Organise the text as required and align middle left. Stop the tooltip from showing. Name the sheet Title or similar.
Building the dashboard and interactivity
Use layout containers to position the objects onto the dashboard, and use padding to provide the relevant spacing between objects.
The Title, Ratings Bar, Monthly Trend and Library Bar sheets should all be in a single vertical container which has a dotted blue border surrounding it.
Create a dashboard parameter action
Set Book
on select of the Scatter sheet, set the pSelectedBookID parameter with the value from the BookID (Editiion) field. When the selection is cleared, retain the value.
The layout of my dashboard including the item hierarchy is below
Hot of the press with the release of 2024.3, Sean set this challenge to focus on the ability to use table extensions. As a result you will need at least v2024.3 of Tableau Desktop or Tableau Desktop Public installed. At the point of writing, this challenge cannot be completed on Tableau Public itself via web authoring though.
Build the scatter plot
Format Sales and Profit to be $ with 2 dp. Add Sales to Columns and Profit to Rows and add Customer Name to Detail. Change the mark type to circle, reduce the opacity to around 30%, add a blue border and increase the size of the marks. Format the zero lines to be more prominent. Add Segment to the Filters , select all options, and set to applyto worksheets > all using this data source
Build the table extension
On a new sheet, choose Add Extension from the marks type drop down, and on the add an Extension dialog, select the built by Tableau + Salesforce option and then select the Tableau Table option
Select Open on the next screen, and then select OK to the next dialog box.
Add Customer Name, Order Date (as a continuous exact date – green pill), Sales and Profit to the Detail shelf.
Move your mouse to be in front of the SUM(Sales) heading text, and then click on the sort icon that appears a couple of times to get the data sorted by Sales descending. Double click on the SUM(Sales) heading label and edit the label to just Sales. Repeat with the SUM(Profit) heading label.
Click on the context menu associated to the Sales column and select Format
Set the Formatting Type to be Data Bars and change the Fill colour to green
Format the Profit column to have a Formatting Type of Colour Scale and select a diverging colour palette
Click the Format Extension button on the Marks card shelf or the Table Settings icon on the formatting toolbar to load the Format Extension dialog
Change the options so Show Toolbar is Off, Show Column Filters is On and Show Excel Download is On
Adding the interactivity
Add the 2 objects into a horizontal container in a dashboard. Float the Segment filter control and verify changing the value affects both the scatter and the table.
Then add a dashboard filter action
Filter Table
On select of the Scatter, target the Table passing all fields. Show all values when selection cleared.
And that should be it. Unfortunately as Tableau Public doesn’t yet support the extension, I don’t have my published version to share.
Note – I did have some issues getting the table to ‘fit’ completely into the dashboard. I found if I used my larger second screen, ensured the application was maximised, then it would fit properly. Using the application on my laptop screen, it was sometimes a bit hit and miss. This has been raised to the development team.
Donna + DataViz 