Let’s practice using the multi-fact relationship model

Donna ColesLeave a comment

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 Base Table 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 total quick 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

My published viz is here.

Happy vizzin’!

Donna

Can you create Stars & Reference lines?

Donna ColesLeave a comment

Lorna provided this week’s challenge, drawing on some tips she’d picked up from Nhung Lee at #TC25. We’re building 2 charts, but for the second we need some supplementary data, and for this we need to start with some data modelling.

Modelling the data

I downloaded the ChatGPT Android reviews data from Kaggle into a csv file called chatgpt_reviews.csv. I also then copied the additional data Lorna provided in the challenge into another csv called WOW2025_19_Additional Data.csv.

I chose to model the data using a physical model rather than relationships, as I needed to ensure all the review data would always exist in the view. To do this I started by connecting to the chatgpt_reviews.csv. From the context menu of the sheet added to the datasource canvas, I selected Open to ‘open’ the physical model ‘window’

I then added a new connection to the WOW2025_19_Additional Data.csv file And added a Left Join to it, joining from the chatgpt_reviews.csv using a join calculation of

DATE(DATETRUNC(‘day’,[At]))

and joining this to the Release Date field from the WOW2025_19_Additioanal Data.csv file

Now with this set up, we can start to build.

Creating the bar chart

Add Score as a discrete dimension (blue disaggregated pill) to Rows and chatgtp_reviews.csv (Count) to Columns.

Add Score as a discrete continuous (green disaggregated pill) to Colour and adjust to use a grey colour palette. Make each row a bit wider, and show labels

Create a new field

Star Rating

REPLACE(SPACE([Score]), ‘ ‘, ‘★’)
+
REPLACE(SPACE(5-[Score]),’ ‘, ‘☆’)

and add this to Rows after Score.

Hide the Score column (uncheck show header), increase the size of the font for the Star Rating field, hide the Count of chatgpt_reviews axis (uncheck show header), hide all gridlines/axis rulers/ zero lines and row and column dividers. Adjust the font style of the label, hide the ‘Star Rating’ column label (right click > hide field labels for rows). AAjust the Tooltip as desired and update the sheet title. Name the sheet star Rating Bar or similar.

Building the line chart

On a new sheet, add At as a continuous pill at the ‘1st May 2025‘ day level (green pill) to Columns and add chatgpt_reviews.csv (Count) to Rows. Change the Colour of the line to dark grey.

For the ‘reference lines’ we’re actually adding bars all at the same height. The height we’re going to use is the maximum number of reviews, so create

Max Review Count

IF NOT ISNULL(MIN([Release Date (WOW2025 19 Additional Data.csv1)])) THEN WINDOW_MAX(COUNT([chatgpt_reviews.csv])) END

Add this to Rows and hide the null indicator (right click > hide indicator). Change the Mark type to bar.

Add Product to the Label of the Max Review Date marks card, and align as below

Adjust the table calculation of the Max Review Count field so it is computing by both the At and Product fields.

Make the chart dual axis and synchronise the axis. Adjust the Label to explicitly add a couple of carriage returns after the Product text. This has the effect of shifting the text to the left of the bars.

Fix the Count of chatgpt_reviews axis from -100 to 10,800

Ensure mark labels are set to allow labels to overlap other marks.

Tidy up by

  • hiding the right hand axis (uncheck show header)
  • Updating the title of both axis (right click > edit axis)
  • remove all gridlines, zero lines, row & columns dividers
  • show axis rulers
  • update tooltips as required
  • update the sheet title
  • name the sheets Trend or similar.

Then add both charts to a dashboard. My published viz is here.

Happy vizzin’!

Donna

Can you filter or drill to details?

Donna ColesLeave a comment

Sean set this week’s challenge to give an alternative solution to displaying a table of details rather than the traditional ‘pancake table’ (his words not mine 🙂 ).

The main crux of the challenge relates to the dashboard actions and interactivity, so I’ll be brief(ish) in describing how to build the charts.

Creating the line chart

Add Order Date to Columns at the month-year continuous (green pill) level. Add Sales to Rows. Format Sales to $ with 0 dp. Remove the title on the Order Date axis. Update the Tooltip to give an instruction to ‘click the line to filter’. Rename the sheet Sales Trend or similar.

Creating the bar chart

Add Sub-Category to Rows and Sales to Columns. Sort by Sales descending. Hide the Sub-Category row heading label (right click > hide field labels for rows). Update the Tooltip to give an instruction to ‘click the bar to filter’. Rename the sheet Sales by Sub Bar or similar.

Creating the Tree Map

Add Segment and Ship Mode to Detail and Sales to Size. Move Segment to Colour and reduce opacity to about 60%. Move Ship Mode to Label and then add additional Segment and Sales pills to Label. Add a table calculation against the Sales pill on the Label shelf, so it is applying a percentage of Total by Segment only.

Add another instance of the Sales pill to Label and then update the layout of the label.

Move the Segment pills on the marks shelf so they are positioned below the Ship Mode to ensure the tree map is segmented based on the Ship Mode (there should be four blocks divided by the thicker white lines).

Update the Tooltip to give an instruction to ‘click the treemap to filter’. Rename the sheet Treemap or similar.

Build the Details table

On a new sheet add Order ID, Customer Name, Order Date (as a discrete exact date – blue pill), Ship Date (as a discrete exact date – blue pill) and Product Name to Rows. Add Sales to Text. Format Profit to $ with 0 dp and drag onto the canvas over the columns of Sales numbers, and release the mouse when the Show Me option appears. Add Discount into the Measure Values section. Change the aggregation to Average and then format to be % to 0 dp. Rearrange the order of the pills in the Measure Values section as required. Add Segment, Sub-Category and Ship Mode to the Detail shelf. Update the title to reference these 3 pills. Hide the Tooltip. Rename the sheet Details or similar.

Building the additional calculations needed

In clicking around Sean’s solution, I was finding what I had initially built wasn’t quite doing what Sean did. If I clicked on the bar chart and then the tree map, the details were only filtered based on the tree map and vice versa. There were ways to solve this, but this then resulted in other issues, in that after closing the details table, the charts remained filtered, but it wasn’t obvious as nothing was highlighted. Basically what I’m trying to say, is the filtering seemed like it should be straightfoward, but wasn’t. I ended up using a combination of parameters and filter actions.

So we’ll start by dealing with the parameters we need.

Create the following parameters

pSelectedDate

date parameter defaulted to 01 Jan 1900

pSelectedSegment

string parameter defaulted to <emptystring>

pSelectedShipMode

string parameter defaulted to <emptystring>

pSelectedSubCat

string parameter defaulted to <emptystring>

Then create the following calculated fields

Filter: Date

[pSelectedDate] = #1900-01-01# OR [pSelectedDate]=DATETRUNC(‘month’,[Order Date])

add this to the Filter shelf on the bar chart, tree map and details sheets and set to True.

Filter: SubCat

[pSelectedSubCat]=” OR [pSelectedSubCat]=[Sub-Category]

add this to the filter shelf on the line chart, tree map and details sheets and set to True

Filter: Segment

[pSelectedSegment]=” OR [pSelectedSegment]=[Segment]

add this to the filter shelf on the line chart, bar chart and details sheets and set to True

Filter: Ship Mode

add this to the filter shelf on the line chart, bar chart and details sheets and set to True

We also need a parameter to capture when we want to show the details table.

pClickMade

boolean parameter defaulted to False.

and to supplement it, we need a calculated field to use to set this parameter to true

Click Made

TRUE

Add Click Made to the Detail shelf of the line chart, bar chart and tree map.

We’ll set these parameters later.

Building the Close icon

The ‘close’ cross when the details sheet is displayed is another sheet. On clicking on it, we will want to set the pClickMade parameter to False so the Details will no longer show. For this we will need

Close

FALSE

Add this field to the Detail shelf on a new sheet. Change the mark type to shape and change the shape to a X. Set the colour to black and set to fit entire view. Hide the Tooltip. Name the sheet Close or similar.

Building the dashboard and interactivity

Using layout containers, arrange the line chart, bar chart and tree map into a dashboard. Use padding and background colours to get the layout as desired.

The add the Details sheet as a floating object and position over the top of the other charts. Set the background to white and add a black border. Also float the Close sheet into position too. Hide the title and also add a black border.

Select the Close sheet object, and then from the Layout tab in the left hand nav, check the Control visibility using value checkbox and select the pClickMade parameter

It should disappear if the parameter is still set to false. Repeat the same process with the Detail sheet object.

Now create the following dashboard parameter actions

Filter Month

On select of the Sales Trend sheet, target the pSelectedDate parameter, passing in the value from the Order Date. When the selection is cleared, reset to 01 Jan 1900.

Filter SubCat

On select of the Sales by Sub Bar sheet, target the pSelectedSubCat parameter, passing in the value from the Sub-Category. When the selection is cleared, reset to <emptystring>.

Filter Ship Mode

On select of the Treemap sheet, target the pSelectedShipMode parameter, passing in the value from the Ship Mode. When the selection is cleared, reset to <emptystring>.

Filter Segment

On select of the Treemap sheet, target the pSelectedSegment parameter, passing in the value from the Segment. When the selection is cleared, reset to <emptystring>.

Drill to Details

Via the menu of the Sales by Sub Bar, Sales Trend, and Treemap sheets, target the pClickMade parameter passing in the value from the Click Made field. When the selection is cleared, set the value to False.

Close Details

On select of the Close sheet, target the pClickMade parameter, passing in the value from the Close field. When the selection is cleared, keep the value.

If you start clicking around, you should find that all these actions do provide some level of filtering, but if you for example, click on the bar (to filter the line and treemap), and then click on a section in the tree map and use the ‘Drill down to details’ menu option, the details table has lost the filtering of the bar chart as the bar has become unselected when the treemap chart was clicked.

To resolve this, apply filter actions to the line chart, bar chart and tree map objects (the quickest way to do this is just select the object on the dashboard and click the ‘filter’ icon in the context menu.

If you do this on all 3 sheets and then look at the list of dashboard actions you’ll see 3 ‘Filter x (generated)’ entries.

By applying this mix of filtering through ‘default’ dashboard filter actions in conjunction with parameters, I think you have a more complete and understandable experience. And you will have to explicitly unselect each of the marks you clicked on to remove that filter. I added instructions on the dashboard to aid with this.

My published viz is here.

Happy vizzin’!

Donna

Can you swap measures?

Donna Coles2 Comments

It’s back to the EFL this week for my #WOW challenge. Once again I’ve tried to provide a multi-level challenge – a version that uses some core skills and features, and a version that just pushes the display a bit further, just for fun. I’ll start with the core build and then use that as the base for the bonus challenge.

Setting up the parameters

The main crux of this challenge is measure swapping, so for this we need a parameter

pMeasure

integer parameter listing values 1 to 4 which are aliased as per the screen shot below. Default value is 1 (Points).

Note – you can use a string parameter with the actual words. I just chose to use this method to demonstrate the aliasing ability. Also when referencing this parameter in a CASE statement (which we’ll do shortly), using integer values for comparisons is slightly more efficient than string comparisons.

We also need the user to have the ability to select the team they want to track

pSelectedTeam

string parameter defaulted to your preferred team (I chose Chelsea). This is a List parameter that is populated from the Team field via the Add values from button

Building the calculations

We need to determine the measure to display based on the parameter selection

Measure to Display

CASE [pMeasure]
WHEN 1 THEN SUM([Cumulative Points])
WHEN 2 THEN SUM([Cumulative Goal Difference])
WHEn 3 THEN SUM([Cumulative Goals For])
WHEN 4 THEN SUM([Cumulative Goals Against])
END

We also will need to colour the bars based on the selected team

Is Selected Team

[Team]=[pSelectedTeam]

and we need to sort the data based on best to worst, but need to consider that for Goals Conceded, the higher the value the worse the team is.

Sort Measure

IF [pMeasure] = 4 THEN [Measure to Display]*-1 ELSE [Measure to Display] END

We only want to show teams once they start participating in a Season. For this, we need to identify the team’s 1st season in the EFL

1st Season per Team

{FIXED [Team]: MIN(IF [Cumulative Points] > 0 THEN [Season End Year] END)}

and then we can create a field we can filter on, based on this

Show Team

[Season End Year]>=[1st Season per Team]

We only want to show a label against the 1st team and the selected team, so create

Label to display

IF MIN([Is Selected Team]) OR FIRST()=0 THEN [Measure to Display] END

and finally, we need to display the value of the current season’s measure on the tooltip, as well as the cumulative value, so we need another case statement

Tootltip Measure

CASE [pMeasure]
WHEN 1 THEN SUM([Points])
WHEN 2 THEN SUM([Goal Difference])
WHEn 3 THEN SUM([Goals For])
WHEN 4 THEN SUM([Goals Against])
END

Building the core bar chart

On a new sheet, add Team to Rows and Measure to Display to Columns. Add Season End Year to Filter and select 1993. Add Show Team to Filter and select True. Apply a sort to the Team field to sort by the field Sort Measure descending.

Add Is Selected Team to Colour and adjust accordingly. Add Label to display to Label. Add Season and Tooltip Measure to Tooltip and update accordingly,

Show the pMeasure and pSelectedTeam parameters and the Season End Year Filter. Adjust the Season End Year filter control so that the All option isn’t available and it displays as a single value slider control.

Move the Season End Year filter control on by one value and notice how the chart transitions. Adjust the Animations settings (Format menu > Animations) to be sequential and slow

Finally tidy up the display by

  • hiding the Measure to Display axis (right click > uncheck show header)
  • hiding the Team row label (right click > hide field labels for rows)
  • widen each row a bit
  • hide gridlines, zero lines, axis rulers and axis ticks
  • add pale grey row dividers
  • set the background colour of the worksheet
  • adjust the font of the row labels – I used Tableau Book 8pt in colour #3e1756 (dark purple)

Name the sheet Core or similar and add to a dashboard setting it to Fit Entire View

Add a title to the dashboard that references the pSelectedTeam parameter.

My core viz is here.

Building the bonus viz

Start by duplicating the core viz sheet.

We’re going to use a gantt bar to simulate a central line for each row. This bar needs to extend to the largest measure in the table for every row, so this is the point we’ll plt

Max Measure

WINDOW_MAX([Measure to Display])

Add this to the Columns shelf, and on the Ma Measure markls card that gets added, remove the Is Selected Team from the Colour shelf, and change the mark type to gantt bar.

The bar needs to extend to the 0 or the minimum value in the window (if its less than 0), so we need a field to show the difference between the max and the minimum of 0 or the ‘window min’, but we need to multiple by -1 so the size extends in the right direction.

Max-Min Diff

([Max Measure] – MIN(WINDOW_MIN([Measure to Display]),0)) * -1

Add this to the Size shelf, and then update the size to be as small as possible. Remove Label to Display from the marks card and add a border the same colour as the background to the Gantt bar (via the Colour shelf) to make the line very narrow.

Add Team to the Label shelf and align centre left. Format the label text to be 8pt and dark purple. Then make the chart dual axis and synchronise the axis.

Right click the top axis and select move marks to back. Then hide both axis (right click, uncheck show header) and hide the Team pill on Rows too (again uncheck show header)

Verify the Tooltip on the gantt bar displays the same as on the main bar. If not add the relevant fields and adjust to match.

Tidy up the formatting by removing the row and column dividers. If need be adjust the colour of the Gantt bar to be a paler grey.

Change the measure value to Goal Difference and adjust the year to 2024 and check the display looks as expected, especially at the bottom – the gap between the label of the team at the bottom and the bar is minimal.

Add the chart to a dashboard – the simplest way is to duplicate the core dashboard and then use the swap sheets feature to quickly swap the main vizzes.

My published viz is here

Happy vizzin’!

Donna

Can you use containers and dynamic zone visibility?

Donna ColesLeave a comment

This week’s #WOW2025 challenge was set live as part of TC25. Unfortunately, this year I couldn’t be there in person to meet everyone, which for the last 3 years has been my conference highlight 😦

Anyway, Kyle set the challenge, and conscious of time, provided a starting workbook, so the focus could be on the container and DZV functionality. For those who nailed this, he added some additional interactivity with dashboard actions.

So the first thing is to download the starter workbook from the challenge page.

I’m going to attempt to build this in the order of Kyle’s requirements.

Layout out the dashboard

So the requirement states that no floating objects are allowed. Typically when I build a dashboard for business purposes or where the layout is a little complicated, I always start by adding a floating container sized to the exact dashboard size and positioned 0,0. I then add tiled objects into it. Doing this means I don’t end up with Tiled container objects on my dashboard (or if any get added when legends/filters get automatically added, I just move any items I want to retain and then delete the Tiled container).

However, as Kyle says ‘no floating’, I will build adding to the ‘default’ dashboard which means there will be containers on there I don’t really want.

Now blogging about containers is usually very tricky as it’s hard to explain where things need to go. So I’ll be supplementing this with a lot of screen shots – fingers crossed following along works out ok!

To start, create a dashboard sheet and resize to 1200 x 900 as required. Observe the item hierarchy section of the Layout pane as this is where you’ll see all the containers and objects as we add them to the dashboard.

The main structure of the display is split into 2 columns, so start by adding a horizontal layout container to the dashboard. Once added, add 2 blank objects side by side to give the basic layout. Adding blank objects helps when positioning the required objects and is recommended when dealing with layout containers, especially if you’re new to them. They will ultimately be deleted as we go. Rename the horizontal container H – 2 cols or similar (right click on the container in the item hierarchy  > rename).

Notice how a Tiled container has now also appeared on the dashboard, even though we only added a horizontal container.

The first column of the dashboard contains 2 charts – the Scatterplot and the Sales & Profit Ratio Comparison sheets – stacked on top of each other. For this, add a vertical layout container between the two blank objects. Rename this V – Col 1.

Add the Scatterplot sheet into the vertical container and then add Sales & Profit Comparison underneath it.

The various legends associated with these 2 sheets, automatically get added into their own vertical container on the right hand side. These aren’t required, so from the item hierarchy, select the Vertical container and then Remove from dashboard.

The right hand column of the display will show the Sales & Profit Ratio by Month sheet and another (hidden) chart that needs to be built.

Add another vertical container between the V – Col 1 container and the right hand blank object. Name this V – Col 2, and add the Sales & Profit Ratio by Month sheet and then another blank object underneath it. Once again remove the right hand vertical container that is automatically added with all the legends/filters.

Now we have the ‘core’ layout, the 2 blank objects we added to the horizontal container, H – 2 Cols, right at the start, can be removed, so hopefully you should have a layout organised as below.

Now add the dashboard title (Dashboard menu > Show Title, and then update the text). This will automatically add a vertical layout container around all the existing contents.

Building the Sales & Profit Ratio by Sub-Category bar chart

On a new sheet, add Sub-Category to Rows and Sales to Columns. Add Profit Ratio to Colour and adjust the colour legend to use the Red-Black Diverging colour palette. Hide the Sub-Category row label heading (right click > hide field labels for rows).

The bar chart needs to be filtered when a State in the Sales & Profit Ratio Comparison chart is clicked on, or when a Date is selected in the Sales & Profit Ratio by Month chart. However, I noticed when clicking around, that when clicking the Sales & Profit Ratio by Month chart, it filtered the above bar chart by both the State and Date. So based on this, create 3 parameters.

S&PR Comp State

String parameter defaulted to empty string

S&PR by Month State

String parameter defaulted to empty string

S&PR by Month Date

Date parameter defaulted to 01 Jan 1900 (essentially a null date)

Show these parameters on the sheet.

We want to filter the chart if the S&PR Comp State has a value and the S&PR by Month Date is the ‘null’ date (which means we’ve interacted with the Sales & Profit Ratio Comparison chart), or if the S&PR Monthly State has a value AND the S&PR by Month Date has a value (which means we’ve interacted with the Sales & Profit Ratio by Month chart). So create

Filter – S&PR by SubCat

([State Name] = [S&PR Comp State] AND ([S&PR by Month Date]=#1900-01-01#))

OR

(([State Name] = [S&PR by Month State]) AND (DATETRUNC(‘month’, [Order Date]) = DATETRUNC(‘month’, [S&PR by Month Date])))

Enter a State name into the S&PR Comp State parameter (eg New Jersey), then add the Filter – S&PR by SubCat field to the Filter shelf and set to True. The chart should change.

Verify the functionality by adding a state and date into the other parameters eg 01 March 2021 and Texas

Empty the state parameters and set the date back to 01 Jan 1900. Name the sheet Sales & Profit Ratio by SubCat. The chart contents will disappear.

Creating a dynamic title sheet

Originally I hoped to do this without using another sheet and just using the title of the bar chart, but I need the date to show nothing rather than Jan 1900 depending on the user interactivity, so a new sheet is required.

But for it, we need some additional calculated fields.

State for Title

IIF([S&PR by Month State]<>”,[S&PR by Month State], [S&PR Comp State])

We only want to show the name of the state once, and both parameters may have it set.

Date for Title

IF [S&PR by Month Date]=#1900-01-01# THEN ” ELSE DATENAME(‘month’,[S&PR by Month Date]) + ‘ ‘ + STR(YEAR([S&PR by Month Date])) END

Line

IF [S&PR by Month Date]<>#1900-01-01# THEN ‘|’ ELSE ” END

Add all 3 fields to the Detail shelf of a new sheet. Change the mark type to polygon. Update the sheet title as below

Name the sheet S&PR Title Sheet or similar

Adding the bar chart, title & legend to the dashboard

All 3 of these objects – the bar chart, the title sheet and the profit ratio legend need to show or hide based on interactivity. To do this in one step, we can encapsulate the 3 objects within containers within another ‘parent’ container and control the visibility on the ‘parent’ container.

Add a vertical container between the Sales & Profit Ratio by Month chart and the blank object. Name this V – S&PR SubCat Chart

Add the Sales & Profit Ratio by SubCat sheet into this. Then add another horizontal container and place it above the Sales & Profit Ratio by Sub Cat chart (making sure it’s within the V – S&PR Sub Cat Chart container. Rename this H – S&PR Sub Cat Title.

Add the S&PR by Title sheet into this horizontal container, and then click on the Profit Ratio legend on the right hand side and move this object to sit to the right of the title sheet. Then click on the right hand column containing all the remaining legends, and delete this container from the dashboard. Then remove the blank object that’s sitting beneath the Sales & Profit Ratio by SubCat sheet. You should have something like below…

Adjust the width of the S&PR Title sheet so its wider. Set the sheet to Fit Entire View. Then select the H – S&PR SubCat Title container and edit the height to be 90 px.

Hide the title of the Sales & Profit Ratio by SubCat sheet.

Hiding and showing the Sales & Proft Ratio by Sub Category section

Create a new calculated field

Show S&PR by Sub Cat

[S&PR by Month State]<>” OR [S&PR Comp State]<>”

On the dashboard, select the V – S&PR SubCat Chart container and on the Layout pane, check the Control visibility using value checkbox, and select the Show S&PR by Sub Cat field. Assuming all the parameters are set to their default values, then the whole section should disappear, although the container will still be selected.

To make the section show, we need to set the parameters using dashboard parameter actions.

Set S&PR Comp State

On select of the Sales & Profit Ratio Comparison sheet, set the S&PR Comp State parameter passing in the value of the State Name field. When the selection is cleared, set the value back to <emptysrting>

Click on a row in the Sales & Profit Ratio Comparison bar chart, and the Sales & Profit Ratio by SubCat chart should display, filtered to that State, with the selected state name in the title.

Click the state again, and the chart disappears.

Create 2 further dashboard parameter actions

Set S&PR by Month State

On select of the Sales & Profit Ratio by Month sheet, set the S&PR by Month State parameter, passing in the value from the State Name field. When the selection is cleared, set it back to <emptystring>

Set S&PR by Month Date

On select of the Sales & Profit Ratio by Month sheet, set the S&PR by Month Date parameter, passing in the value from the Month([Order Date]) field. When the selection is cleared, set it back to 01/01/1900

Now click on a point in the line chart, and the Sales & Profit Ratio by SubCat chart should display filtered to the relevant state and month

Adding the Additional Interactivity

When the Scatterplot is clicked, the State in the existing Scatterplot State parameter should be updated. Create a dashboard parameter action

Set Scatterplot State

On select of the Scatterplot sheet, set the Scatterplot State parameter, passing in the value from the State field. When the selection is cleared, retain the value

If you click around the scatterplot, the Sales & Profit Ratio by Month line chart and Sales & Profit Ratio Comparison charts should update.

But we don’t want the other marks on the scatter plot to ‘fade’. To solve this, create a dashboard filter action.

Deselect Scatter marks

On select of the Scatterplot sheet on the dashboard, target the Scatterplot sheet directly, setting the fields TRUE = FALSE. On clearing the selection, show all values.

Finally, the last requirement is to highlight the line in the Sales & Profit Ratio by Month chart associated to the State selected in the Sales & Profit Ratio Comparison chart. For this first create a dashboard set action to capture the selected state

Add State to Set

On select of the Sales & Profit Ratio Comparison sheet, target the State Name Set. Check the single-select only checkbox. Running the action should Assign value to set and clearing the selection should remove all values from set

Then add a dashboard highlight action

Highlight Monthly Trend Chart

On select of the Sales & Profit Ration Comparison sheet, target the Sales & Profit Ratio by Month sheet targeting the State Name field only

And hopefully, with all this, you should have a fully interactive dashboard. My published viz is here.

Happy vizzin!

Donna

Visualising population by age and gender in a population pyramid

Donna ColesLeave a comment

For this week’s challenge, we’re still in community month, so guest poster, Anna Clara Gatti, provided us with this challenge – to represent the spread of population in a pyramid. Anna provided 3 levels to the challenge, so I’m going to write this blog, building on each challenge.

Beginner Challenge

Defining the calculations

To start this we need to create some calculations. Firstly we have measures Male and Female which provide us with the population for each of the associated genders. But we need to ascertain the total population for each Country and Year so we can then calculate a % of total. So we need

Total Population

{FIXED [Country], [Year]: SUM([Male]) + SUM([Female])}

and from this we can then define

Male – % of Total Population

SUM([Male])/SUM([Total Population])

format this to a % with 1 dp

Female – % of Total Population

SUM([Female])/SUM([Total Population])

format this to a % with 1 dp

These are the core measures we want to plot, but we want to plot this against the Age. But the Age provided is ‘text’ and not a pure number. So to make life easier, we’re going to extract the number out

Age Axis

IF [Age] = ‘Less than 1 year’ THEN 0
ELSEIF [Age]= ‘100+ years’ THEN 100
ELSE INT(LEFT([Age],FIND([Age],’ ‘,1)-1)) END

Move this into the Dimensions section of the data pane (above the line ).

Now we can start to build the required viz.

Building the Pyramid

On a new sheet add Country to the Filter shelf and select Italy. Add Year to the Filter shelf. By default it will add as a green continuous pill. Just click OK to the filter dialog presented.

Then right click on the Year pill in the Filter shelf, and select discrete. Another filter selection dialog will appear. Select 2024.

Add Age Axis to Rows and change to be a continuous (green) pill. Add Male – % of Total Population to Columns, and then add Female – % of Total Population to Columns too.

Change the mark type on the All marks card to bar and reduce the size a bit. Add Measure Names to Colour and adjust accordingly.

Right click on the Male – % of Total Population axis and

  • Fix the axis from 0 to 0.01
  • Reverse the scale
  • Change the axis title

Then edit the Female – % of Total Population axis and fix the scale from 0 to 0.01 and rename the axis title to give you a symmetrical display

Add the following fields to the Tooltip of the All marks card, and then update accordingly :

  • Male
  • Female
  • Female – % of Total Population
  • Male – % of Total Population
  • Age

Finally tidy up the display by removing row & column dividers, hiding the Age Axis (right click > uncheck show header) and removing the row gridlines.

Building the KPI

On a new sheet add Total Population to Text. Revert back to the Pyramid sheet, and set the both the filters to also apply to new KPI sheet you’re creating.

Update the text to include the ‘Total Population’ label and adjust size of font and align middle centre. Change the mark type to shape and select a transparent shape (for details on how to do this refer to this blog). Set the sheet to Entire View then apply a grey background. Ensure the Tooltip doesn’t show.

Creating the dashboard

Add the Pyramid chart and the KPI chart to a dashboard. I used a vertical layout container to organise the ‘rows’ , and then placed horizontal containers within, so I had a ‘row’ to show the filter controls, and a ‘row’ to show the KPI and then a ‘row’ to display the chart.

In the ‘row’ showing the KPI I added a text object alongside and used the wingdings character set to display a square symbol which I could then just colour to represent the legend.

My beginner version of the viz is here.

Intermediate Challenge

Colouring the pyramid

For this challenge, I started by simply duplicating the Pyramid built for the Beginner challenge. I then created a new field

Age Bracket

IF [Age Axis] <=14 THEN ‘Young’ ELSEIF [Age Axis] >= 65 THEN ‘Elder’
ELSE ‘Active population’
END

and added this to the Detail shelf of the All marks card. I then added this to be on the Colour shelf as well as Measure Names by updating the symbol to the left of the pill

Adjust the colours on the colour legend accordingly, and then update the Tooltip to include a reference to the Age Bracket.

Building the dashboard

Duplicate the original dashboard you built, then swap the pyramid chart by clicking on the Pyramid chart object in the dashboard. Then on the left hand side in the dashboard pane, click on the 2nd pyramid sheet, and click the ‘swap’ icon to replace the chart..

Remove the title that’s automatically added. Then update the legend text box.

My Intermediate version is here.

Advanced Challenge

Defining the additional fields

For this challenge, we’re going to need a lot more fields, including the following parameters

Country 1

string parameter, that is a list, and is populated from the Country field when the workbook is opened. Defaulted to Italy.

Country 2

As above but defaulted to Austria

Year 1

integer parameter, that is a list that is populated from the Year field when the workbook is opened. Defaulted to 2024 that is formatted to display as a number with 0 dp and no thousands separator.

Year 2

As above, also defaulted to 2024.

With these parameters, we also need to redefine the the total population values

Total Pop – Year & Country 1

IF [Year] = [Year 1] and [Country] = [Country 1] THEN [Total Population] END

Total Pop – Year & Country 2

IF [Year] = [Year 2] and [Country] = [Country 2] THEN [Total Population] END

and we also need to capture the male and female populations for these parameters

Male Pop – Year & Country 1

[Year] = [Year 1] AND [Country] = [Country 1] THEN [Male] END

Male Pop – Year & Country 2

[Year] = [Year 2] AND [Country] = [Country 2] THEN [Male] END

Female Pop – Year & Country 1

[Year] = [Year 1] AND [Country] = [Country 1] THEN [Female] END

Female Pop – Year & Country 2

[Year] = [Year 2] AND [Country] = [Country 2] THEN [Female] END

and then in turn, we redefine the % of total calculations

Male – % of Total 1

SUM([Male Pop – Year & Country 1])/ SUM([Total Pop – Year & Country 1])

Male – % of Total 2

SUM([Male Pop – Year & Country 2])/ SUM([Total Pop – Year & Country 2])

Female – % of Total 1

SUM([Female Pop – Year & Country 1])/ SUM([Total Pop – Year & Country 1])

Female – % of Total 2

SUM([Female Pop – Year & Country 2])/ SUM([Total Pop – Year & Country 2])

Set all the % of total calcs to be % with 1 dp.

Building the Pyramid

Once again start by duplicating the pyramid chart sheet built for the intermediate solution

Drag Male – % of Total 1 and drop it directly onto the existing Male = % of Total Population pill on the Columns shelf, so it basically replaces it.

Repeat the process by dragging Female – % of Total 1 and dropping it directly over Female – % of Total Population. Adjust the colours if required.

Remove the Country and Year field from the Filter shelf and show the 4 parameters.

Drag Male – % of Total 2 and add to Columns between the two existing pills.

On the Male – % of Total 2 marks card, change the mark type to Line , remove Measure Names from Colour and move Age Bracket to Tooltip. Change the colour to dark grey/black.

Make the % of total male pills dual axis and synchronise the axis.

Repeat the process by adding Female – % of Total 2 to Columns to the right hand side of the existing Female field, adjusting the mark type and colouring and making dual axis.

Right click on the top axis and uncheck show header to hide them. Then right click on the Male – % of Total 1 axis at the bottom, as as before, fix the axis from 0 to 0.01, reverse the axis, and update the title. Repeat for the Female axis (though don’t reverse).

On the All marks card, make sure all the following fields exist on the tooltip, then adjust the tooltip as required, referencing the parameters as well.

  • Male Pop – Year & Country 1
  • Male Pop – Year & Country 2
  • Female Pop – Year & Country 1
  • Female Pop – Year & Country 2
  • Male – % of Total 1
  • Male – % of Total 2
  • Female – % of Total 1
  • Female – % of Total 2
  • Age
  • Age Bracket

Building the KPI cards

Duplicate the original KPI card

Drag Total Pop – Year & Country 1 directly onto the existing Total Population field. Then add the Country 1 and Year 1 parameters to the Label shelf, and update the text as required. Remove the fields from the Filter shelf.

Repeat this process again but add the appropriate ‘2’ versions of the fields to create the 2nd KPI card.

Building the dashboard

Once again, duplicate the dashboard and swap the pyramid charts. Replace the filter controls (if still present) with a row of parameter controls.

Swap/add the KPI sheets, and add an additional Text object. Update the text to display the ‘legends’ as required.

I chose to add navigation buttons to my dashboard to move between the 3 versions of the challenge.

My advanced version is published here.

Phew! a lot going on here it seems. Hope you managed it all!

Happy vizzin’!

Donna

Which products have low profit ratio?

Donna ColesLeave a comment

Community month continues this week with Yama G posing us this challenge to analyse profit ratio.

Building the basic bubble chart

The simplest way I found to start this, was to use ctl-click to multi-select Category, Region, Product Name and Sales from the data pane, and then select the packed bubbles option from the Show Me menu.

Then move Category from Text to Columns and Region from Text to Rows, and remove Category from Colour.

Create a new field

Profit Ratio

SUM(Profit)/SUM([Sales])

and apply a custom number format of 0%;â–²0%;0% (in this instance I think the intention is to use the â–² as ‘warning’ indicator).

Add this to Colour. To cluster the lower profit ratios towards the centre of the bubbles, add a sort to the Product Name pill on the Text shelf, to sort ascending by Profit Ratio.

Note – you can’t influence quite how the bubbles are arranged, so it’s possible the layout you see may differ slightly from the solution.

Showing details for selected Sub-Category

Clicking on a Category in the bubble viz should expand to show the Sub-Categories. To support this we need a parameter

pCategory

string parameter defaulted to Technology

The Category label displayed needs to show an arrow indicator based on whether the Category is selected or not

Label – Cat

[Category] + ‘ ‘ + IIF([Category]=[pCategory],’â–²’,’â–¼’)

Add this to Columns

We want to show Sub-Category details for the selected Category, so create

Label – SubCat

IIF([pCategory]=[Category], [Sub-Category], ”)

Add this to Columns too.

There is a need to capture the Region too, as part of the sorting on the tabular viz. But the Region header label needs to indicate the selected region. We will need a parameter

pRegion

string field defaulted to East

And then a new field

Region * Sortkey

[Region] + IIF([pRegion]=[Region],’*’,”)

Add this to Rows.

Finally tidy up by

  • adjusting the Tooltip
  • hide the Region pill (uncheck show header)
  • hide the Category pill (uncheck show header)
  • hide the Region * Sortkey row heading label (right click -> hide field labels for rows)
  • hide the Label – Cat / Label SubCat column heading label (right click -> hide field labels for columns)
  • remove the row dividers in the body of the viz (set the Level to 0)

Update the sheet title and name the sheet Bubble or similar.

Building the basic Product Detail table

The first column is a combination of fields, so create

Product Name (Cat, SubCat)

[Product Name] + ‘ (‘ + [Category] + ‘,’ + [Sub-Category] + ‘)’

Add Product Name, Product Name (Cat, SubCat) and Region * Sortkey to Rows and Order Date as a discrete (blue) pill at the Year level to Columns. Change the mark type to circle. Add Sales to Size and Profit Ratio to Colour. Add Profit Ratio to Label and align right. Set the table to Fit Width. Increase the Size of the circles a bit.

Add totals via Analysis > Totals > Show Row Grand Totals and then via the same menu, select the Row Totals to Left option. Edit the Colour Legend and select the Include Totals option, so the circles in the total column are also coloured.

Applying the table sort

The requirement is to sort each Product Name based on the value of the Profit Ratio associated to the selected Region. The data should be sorted ascending.

This took me a little while to figure out – initially I wanted to use a table calculation, but you can’t apply a sort to a field based on that, and if you then add it as a discrete measure, you can’t have total columns. So I figured it out eventually. Firstly, I need to capture the Profit Ratio for each Product Name and Region (essentially the value listed in the Grand Total column). I can use an LOD for this.

PR by Product & Region

{FIXED [Product Name], [Region]: SUM([Profit])/SUM([Sales])}

but I then only want the value associated to the selected Region, and I want to ‘spread’ this across every row for the Product Name. So I can use another LOD but just at the Product Name level, which in turn uses a nested IF statement, to just return the value we care about.

Sort

{FIXED [Product Name]: AVG(IIF([Region]=[pRegion],[PR by Product & Region],NULL))}

Apply a Sort to the Product Name pill in Rows to sort by the field Sort ascending

You should find that when you see some Product Names that have sales in the selected region (in this case East), that the products are listed based on this value with the lowest Profit Ratio first (note the East row isn’t listed first if there are sales for the Central region, as the rows within each product are listed alphabetically based on the region name.

Finally tidy up by

  • Adjusting the Tooltip to suit
  • Hide the Product Name pill (right click, uncheck show header)
  • Adjusting the font style of the fields and label headings.
  • Rename the Grand Total label to Total (right click the label > format and update the Label value)
  • hide the Order Date column heading label (right click > hide field labels for columns)
  • add row banding (right click viz to format), then adjust band size to level 1

Update the sheet title and name the sheet Table or similar.

Building the dashboard and adding the interactivity

Using layout containers, add the two sheets onto the dashboard, adding a title and the relevant legends (note I used text objects for the Profit Ratio legend title and the Sales ‘legend’.

We need several dashboard actions:

Filter Products

Dashboard filter action, that on select of the Bubble sheet, targets the Table sheet, passing through the Product Name only. The set of products is retained when the selection is cleared.

Set Category

Dashboard parameter action, that on select of the Bubble sheet, sets the pCategory parameter with the value from the Category field. When the selection is cleared, the parameter is reset to <empty string>

This action will allow the bubble chart to ‘expand and collapse’ on click.

Set Region

Dashboard parameter action, that on select of the Bubble sheet, sets the pRegion parameter with the value from the Region field. When the selection is cleared, the parameter is retained

This action will apply the ‘niche’ sort and the relevant region labels will be updated with an * too.

Finally, on selection of products in the bubble viz, we don’t want the other marks to ‘fade’ To stop this from happening, create a new field

Dummy

‘Dummy’

and add to the Detail shelf on the Bubble sheet.

Then add a dashboard highlight action

UnHighlight

On select of the Bubble sheet, target the Bubble sheet selecting the field Dummy only

And with that you should have a functioning dashboard. My published viz is here.

Note, after testing, I did notice a difference in the behaviour of my version to the solution. When I deselect all products from the bubble chart when in an expanded state, the section will collapse. The solution uses a ‘fake header’ sheet to stop this from happening, which is then carefully positioned above the bubble chart sheet. I’m ultimately happy with my 2-sheet solution, but feel free to check out the challenge solution for a better understanding.

Happy vizzin’!

Donna

Data Storytelling with Dynamic Zone Visibility

Donna ColesLeave a comment

This week’s community challenge was set by Chiaki to showcase how to use dynamic zone visibility to tell a story. We’ll step through each chart and then how it’s all put together.

Building the line chart

Create a new field

Profit Ratio

SUM(Profit)/SUM(Sales)

and format to % with 1 dp.

Add Order Date to Filter and restrict to Years 2023 and 2024 only.

Add Order Date to Columns as a discrete (blue) pill at the year level. Add Sales to Rows and Profit Ratio to Rows. Format Sales to be $ with 0 dp. Set the screen to Fit Width.

Add Sales to the Label shelf of the Sales marks card. Add Profit Ratio to the Label shelf of the Profit Ratio marks card.

Create a new field

Profit Ratio YoY

ZN([Profit Ratio]) – LOOKUP(ZN([Profit Ratio]), -1)

and format to % with 1 dp. Add to the label shelf of the Profit Ratio marks card

Right click on the 2024 Profit Ratio mark and select Annotate > Mark. Update the text as required and reference the value of Order Date year and the Profit Ratio YoY

Click and drag the annotation to reposition, and then right click on the annotation to format, and adjust the shading to pale orange and add a thick orange border.

Hide the Order Date column heading (right click > hide field names for columns). Add a title and name the sheet Line or similar.

Building the comet chart

On a new sheet, add Sales to Columns and Profit Ratio to Rows. Add Order Date to Filter and restrict to years 2023 & 2024 only. Add Sub-Category to Detail and change the mark type to Line.

Add Order Date to Detail as a discrete (blue) pill at the Year level, and add another instance of Order Date at the Year level to Size.

Add Profit Ratio to Colour. Add Profit Ratio YoY to Tooltip and adjust the table calculation so it is computing by Year of Order Date only.

Update Tooltip as required and then add annotations to the required marks, again making references to the relevant variables. Format as before, add a title and name the sheet Comet or similar.

Building the Sankey chart

On a new sheet, add Order Date as a discrete (blue) pill to Filter and restrict to years 2023 and 20204. Add Sub-Category and restrict to Tables and Binders only.

Create a new field

Profitable

IF [Profit] >0 THEN ‘Profitable’ ELSE ‘Unprofitable’ END.

From the dropdown in the marks card, select Sankey

Then add Profitable, Order Date and Region to the Level shelf in that order. Adjust the Sort of the Profitable pill, so its sorted descending and Unprofitable is listed before Profitable.

Add Profit Ratio and Orders(Count) to the Tooltip and update accordingly. Click Format Extension and set the Level Padding and the Link Padding to 10 each.

Click on the Unprofitable box to highlight it and the subsequent flow. Add a title and name the sheet Sankey or similar.

Controlling the story

Create a parameter

Page

integer parameter from 1-4 where each number is mapped to the relevant text. Default to the text associated to 1.

Create boolean fields

Page >=2

[Page]>=2

Page >= 3

[Page]>=3

Page = 4

[Page]= 4

Building the dashboard

You need to use layout containers to build the dashboard. When using these, it’s often useful to add blank objects to help preserve the type of container you want as you add things in. These then get removed. It can be quite fiddly to get right.

For the main body of the dashboard, the section with the charts in, I started with a horizontal container that then had a vertical container on the left and the Sankey on the right. The left hand vertical container then had the line chart on the top, and another horizontal container beneath it. That horizontal container then had the comet chart on the left and a vertical container on the right, which contained the colour and size legends.

I have a habit of renaming the layout containers in the item hierarchy section, to help me identify each section

The line chart object was formatted to have a grey border, outer padding of 10 and inner padding of 20. The vertical container around the comet chart and it’s legends, was also formatted the same. And the Sankey chart object was also formatted the same.

Add a title section, and show the Page parameter as a single value list. Format this with a grey background.

Click on the layout container that contains the comet chart and it’s legends, and then from the layout tab on the left hand side, check the control visibility using value and select the Page >=2 option.

If you page control is set to 1 (Problem Statement), then this section will now disappear. Set it to the next option for it to reappear.

Click on the Sankey object, and do the same, although this time select the Page >=3 option. Again changing through the page control options, the chart will appear and disappear.

With all three charts displaying, and once you’re happy with your layout, add a floating text box and resize to cover the main chart areas. Add the relevant text, set the background colour to grey with a 97% opacity, so the charts underneath can just be seen. Then set the visibility of this to use the Page =4 option.

Cycle through the page options and see how the display changes. My published viz is here.

Happy vizzin’!

Donna

Can you show the top X customers in EACH of the last 3 years and their contributions to Sales?

Donna ColesLeave a comment

For Community Month at #WOW2025 towers, Lorna presented a challenge one of her colleagues had brought to her which they solved together. The need is to identify the top X customers in each year (which may not contain the same set of customers each year), and then present the sales contribution, either as a group or individually compared to the rest. Lorna gave a hint in the challenge that sets would help : “Your job is to figure out the best way to SET this up with the last 3 years dynamically”.

It took me a bit of a while to figure out how to make this work, and at the point of writing, haven’t looked at the solution to know if there was a better way. I ultimately ended up creating 3 sets to fulfil this challenge.

Setting up the parameters

This challenge requires 3 parameters

pTop

This identifies how many ‘top’ customers we want to consider. Defined as an integer from 10 to 100, defaulted to 20, that increments every 10 units

pShowCustomers

Determine whether the top customers’ contributions are displayed individually or as a group. Defined as a boolean, defaulted to False, and aliased to Yes or No

pPercentofTotal

Indicate whether the information is displayed as a % of total sales for that year, or as absolute sales values. Defined as a boolean, defaulted to True, and aliased to Yes or No.

Defining the core calculations

The requirement states to be able to determine the last 3 years ‘dynamically’. For this I created

Max Date

{FIXED: MAX([Order Date])}

to return the maximum Order Date in the whole data set.

We want to be able to restrict the data to the last 3 years, so create

Records to Show

DATEDIFF(‘year’, [Order Date], [Max Date]) <=2

I need to create a set for each of the 3 cohorts – the top customers for the latest year, the top for the previous year and the top for the year before that. For this I first need to determine the Sales for each of those timeframes.

The sales for the current year

Sales – CY

IF YEAR([Order Date]) = YEAR([Max Date]) THEN [Sales] END

The sales for the previous year

Sales – PY

IF YEAR([Order Date]) = YEAR([Max Date])-1 THEN [Sales] END

and the sales for the previous previous year

Sales – PPY

IF YEAR([Order Date]) = YEAR([Max Date])-2 THEN [Sales] END

I can then create the sets of customer I need (right click on Customer ID > Create > Set)

Customer ID Set – CY

get the Top number of records using the pTop parameter, and based on the sum of the Sales – CY field

Repeat the same process to create

Customer ID Set – PY

get the Top number of records using the pTop parameter, and based on the sum of the Sales – PY field

and

Customer ID Set – PPY

get the Top number of records using the pTop parameter, and based on the sum of the Sales – PPY field

To verify/understand what we’ve created, on a new sheet

  • Add Customer ID to Rows
  • Add Order Date to Columns at the Year level as a discrete (blue) pill
  • Add Records to Show to Filter and set to True.
  • Add Sales to Text.
  • Sort by the 2024 Sales value descending.
  • Add Customer ID Set – CY to Rows.

You should see the first 20 rows (assuming you haven’t changed the pTop value, display as In

If you now change the sort to sort by 2023 Sales descending, and swap the Customer ID Set – CY with the Customer ID Set – PY, you’ll get the same

So now that’s understood, we want to tag each of our customers based on the year of the order, whether they’re in the top n or not, and whether we want to display the customers individually or not

Group – Detail

IF (YEAR([Order Date]) = YEAR([Max Date]) AND [Customer ID Set – CY]) THEN
IF [pShowCustomers] THEN [Customer ID]
ELSE ‘Top N’
END
ELSEIF (YEAR([Order Date]) = YEAR([Max Date])-1 AND [Customer ID Set – PY]) THEN
IF [pShowCustomers] THEN [Customer ID]
ELSE ‘Top N’
END
ELSEIF (YEAR([Order Date]) = YEAR([Max Date])-2 AND [Customer ID Set – PPY]) THEN
IF [pShowCustomers] THEN [Customer ID]
ELSE ‘Top N’
END
ELSE
‘Other’
END

We’re also going to want to count customers, so need

Count Customers

COUNTD([Customer ID])

On a new sheet add Order Date at the year level as a discrete (blue) to Rows and add Group Detail to Rows too. Add Records to Display to Filter and set to True. Add Sales and Count Customers into the table. Show the pTop and pShowCustomers parameters

When pShowCustomers is set to No, you should just see 2 groupings per year

When set to Yes, you’ll get the Customer IDs listed

Note – the Sales numbers should reconcile to the solution – the count might not, which I believe is due to the solution counting distinct Customer Names rather than Customer ID.

To finalise the core calculations we need to build the initial viz, we have a different display depending whether we’re displaying the absolute or % Sales values.

Create

Sales % Total

SUM([Sales]) / TOTAL(SUM([Sales]))

format to decimal to 2 dp and add into table, adjusting the table calculation so it is computing by the Group – Detail only, so the percentage per year is being displayed.

Then we need

Measure to Plot

IF [pPercentofTotal] THEN [Sales % Total]
ELSE SUM([Sales])
END

format this to a number to 2 dp (just so you can see it has a value) and add to the table, applying the same table calculation settings. Display the pPercentofTotal parameter and flip between to see the column change.

Building the Viz

On a new sheet, add Records to Show to filter and set to True. Add Order Date at the year level as a discrete (blue) pill to Rows. Add Group – Detail to Detail. Change the mark type to bar. Add Measure to Plot to Columns and adjust the table calculation, so it’s computing just by Group-Detail.

Ste the sheet to fit width and show the 3 parameters.

Create a new field

Group – Top N

IF (YEAR([Order Date]) = YEAR([Max Date]) AND [Customer ID Set – CY]) THEN ‘Top N’
ELSEIF (YEAR([Order Date]) = YEAR([Max Date])-1 AND [Customer ID Set – PY]) THEN ‘Top N’
ELSEIF (YEAR([Order Date]) = YEAR([Max Date])-2 AND [Customer ID Set – PPY]) THEN ‘Top N’
ELSE ‘Other’
END

and add to Colour, adjusting the colours to suit. You’ll then need to update the table calculation of the Measure to Plot field to ensure Group – Top N is also checked.

We need to display labels, but these need to differ based what measure we’re showing, and the format is different, so create

Label – % Total

IF [pPercentofTotal] THEN [Sales % Total] END

format this to % with 1 dp and

Label – Sales

IF NOT([pPercentofTotal]) THEN [Sales] END

format this $ K to 1 dp.

Add both of these to the Label shelf and ensure they are listed directly side by side. Only 1 will ever actually display.

Change the pShowCustomers to Yes, and then add a white border via the Colour shelf. Add a Sort to the Group – Detail pill to sort by Sales ascending.

Add Sales, Sales % Total and Count Customers to the Tooltip shelf. additionally create

Tooltip – Customer

IF [pShowCustomers] AND [Group – Detail] <> ‘Other’ THEN [Customer Name] END

and add this to Tooltip too. Adjust the Tooltip to suit (make sure Sales % Total) is computing by both Group – Top N and Group – Detail so has the correct numbers.

Finally, hide the axis (uncheck show header on the Measure to Plot pill) and hide the Order Date label (right click and hide field label for columns).

Then add the sheet to a dashboard, and arrange the parameters suitably.

My published viz is here.

Happy vizzin’!

Donna

Can you find sales per customer per first purchase cohort?

Donna ColesLeave a comment

Kyle set the #WOW2025 challenge this week, using a summarised data set based on Superstore that he included in the challenge page.

Building out the core data fields

I started this challenge, by building out the data in a tabular format, so I could verify the calculations I needed. I focused on the ‘month’ level to start with before tackling the ‘year’ level which had the added requirement of containing ‘complete years only’, which I did find a bit tricky. Anyway, to start we need to determine the number of months between the First Order Date and the Order Date.

Months from 1st Purchase

DATEDIFF(‘month’, [First Order Date], [Order Date])

and we also need to identify the number of customers

Move this pill into the Dimension section of the left hand pane (drag it up to the top section above the line)

Count Customers

COUNTD([Customer ID])

and calculate the sales per customer

Sales per Customer

SUM([Sales])/[Count Customers]

format this to $ with 0 dp. Also format Sales to $ with 0 dp.

On a new sheet, add First Order Date to Rows as an exact date dimension (blue pill). Add Months from 1st Purchase Date to Rows. Then add Count Customers, Sales and Sales per Customer into the view.

Add First Order Date to the Filter shelf and select the Month/Year format, then select the 4 months Kyle used – Jan 2021, May 2021, Jun 2022, Nov 2023). Show the filter on the sheet.

Building the Month Level Viz

With the information we have, we can build out the viz at the ‘month’ level of granularity.

On a new sheet, add Months from 1st Purchase Date to Columns and Sales per Customer to Rows. Add First Order Date to the Filter shelf at the Month/Year level and restrict to the relevant dates. Show the filter control. Add First Order Date to Colour and set to the Month/Year level as a discrete (blue) pill. Adjust the colours to suit. I used colours from a palette called CB_Paired I had installed.

Set Stacked Marks to Off (Analysis Menu > Stack Marks > Off).

Create another instance of First Order Date (right click the field and select Duplicate) to crate First Order Date (copy). Rename this First Order Date (for Size). Add to the Size shelf, and set it to be at the Month/Year level and discrete (blue) pill.

Adjust the Sort on the First Order Date (for Size) pill to be by Data source order Descending. This now makes January wider than November.

And then add First Order Date to the Detail shelf at the Month/Year level as a discrete (blue) pill. By default this pill is sorted ascending, and has the effect of moving January to the back and November to the front so all the bars (at least for the 0 entry) are visible. This is why we needed a duplicate instance of First Order Date and we needed it to be sorted in one direction to make the correct months at the front, and in another direction to get the required bar widths.

Add Sales and Count Customer to the Tooltip shelf and adjust the Tooltip as required.

Handling the ‘Year’ requirement

Firstly for this, we need a parameter to drive the change in visual.

pGrain

string parameter listing ‘month; and ‘year’ and defaulted to ‘year’

Our Columns is currently listing the Months from 1st Purchase. We need this to be reflective of the years from 1st purchase instead based on the parameter selected.

Years from 1st Purchase

FLOOR([Months from 1st Purchase]/12)

this rounds the calculation down to the nearest whole number.

Move this into the dimension section of the data pane, and add to Rows of our tabular display, so you can see what it’s doing.

Our X-Axis will then be based on either of these 2 fields

X-Axis

IF [pGrain] = ‘month’ THEN [Months from 1st Purchase]
ELSE [Years from 1st Purchase]+1
END

Add this into the table, and show the pGrain parameter and see the change as you flip the options.

We then need to handle the ‘incomplete year’ requirement. This did take some trial and error, and I’m not totally convinced what I’ve done will work in all scenarios, but it seems to match Kyle’s results for the spot checks I did…

I started by determining the maximum month from 1st purchase in each cohort.

Max months per cohort

{FIXED [First Order Date]: MAX([Months from 1st Purchase])}

Add this into the table as a discrete dimension (blue pill), and each row should match the final Months from 1st Purchase value for each First Order Date

I then created a field to identify which of the rows in the data I wanted to keep. So in the case of the above examples for 01/11/2023, I want the rows where Months from 1st Purchase is from 0 to 11 as these represent a whole year’s worth of data for Years from 1st Purchase = 0 (even though there isn’t a a purchase every month in the year). I ended up doing this with the following calculation

Identify Cut Off

[Months from 1st Purchase] < 12 * (FLOOR(([Max months per cohort]+1)/12))

so based on the above example for 01/11/2023, get me all the rows where Months from 1st Purchase is less than 12 * (FLOOR((13+1)/12) = 12 * (FLOOR(14/12)) = 12 *1 = 12.

Add this into the table

I then created an additional field to filter by

Records to Display

[pGrain] = ‘month’ OR [Identify Cut Off]

and added this to the Filter shelf and set to True.

When we’re at the ‘month’ level, all rows will be included, otherwise, just those we identified will be.

So now we have this, we can adjust the visual (you may choose to take a copy of what was initially built, just in case).

Adjusting the Viz for the Granularity parameter

Show the pGrain parameter on the viz sheet and set to Year. Add Records to Display to the Filter shelf and set to True. Add X-Axis to Columns and remove Months from 1st Purchase Date. Set sheet to Fir Width.

Test changing the parameter and altering the selected dates and verify the behaviour is as expected.

Finally tidy up the formatting – remove all gridlines; edit the axis and remove the title; hide the X-Axis label heading (right click > hide field labels for columns). Add to a dashboard and arrange the colour legend in a single row, and set the First Purchase Date filter to be a multi-value dropdown.

My published viz is here.

Happy vizzin’!

Donna