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

Can you rank by multiple measures?

Donna ColesLeave a comment

This week’s challenge once again has 3 levels, so I will blog the first solution and then build on/adapt from there. The main requirement is focused on working out how to rank the football teams based on multiple measures, and this is relevant for all the levels. Once again, we’re looking at data related to football teams in the English Premier league, and where they finished each season.

Defining the calculation to rank by

A team’s finishing position is based on a combination of points scored, goal difference and goals scored. But on rare occasions, a team is deducted points in the season, so we need to account for that.

Adjusted Points

SUM([Points]) – ZN(SUM([Points Deducted]))

Putting the key measures (Adjusted Points, Goal Difference and Goals For) per Team into a tabular view for just a single Season (eg 1993), we can see multiple instances of teams ending on the same number of Adjusted Points

Just ranking on this field, won’t necessarily order the teams in the correct order as Goal Difference and Goals For also need to be taken into consideration. We need to create a field we can rank based on a combination of all three fields, and to do this we’ll ‘weight’ each field by multiplying each field by a suitable proportion based on precedence. In the example shared via the community forum post the leading indicator was multiplied by 1000. In my case I’m going to divide.

Adj Points | GD | GF

[Adjusted Points] + (SUM([Goal Difference])/100) + (SUM([Goals For])/10000)

Add this field into the table, and sort by it descending. You can see that for the 3 teams with 52 points, two of those teams have the same Goal Difference too, but are being correctly ordered based on the Goals For due to the result of the Adj Points | GD | GF calculation

Now we’ve got the core calculation nailed, we can start to build the viz.

Building the Beginner viz

On a new sheet, add Team to Rows and Season End Year to Columns.

We want to be more specific about the season, so create

Label Season

STR([Season End Year]-1) + ‘-‘ + RIGHT(STR([Season End Year]),2)

and add to Columns too.

To make the ‘squares’ rather than use the square mark type which can sometimes be a bit fiddly to get the size just right, I’m going to use a fake axis and a bar mark type.

Double click into Columns and type MIN(1.0) and change the Mark type to Bar. Adjust the Size to be as big as possible. Edit the axis to be fixed from 0-1.

Hide the Season End Year (right click the pill and uncheck Show Header). Rotate the Label Season (right click on the actual label and Rotate Label). Then make each column narrower and widen each row until you’re happy with the sizing.

We want to the Teams to remain listed in alphabetical order, but we want to know the position the team finished in the season, so we create

Rank Desc

RANK([Adj Points | GD | GF], ‘desc’)

Initially add this to Label, and then adjust the table calculation so it is explicitly computing by Team only.

You can verify the number associated to the teams in the 1993 season with the sorted table you made earlier.

But we’re not going to show the rank for every square, so move the Rank Desc from Label to Tooltip.

We do want to label just the team in first though so create

Is First

IF [Rank Desc] = 1 THEN ‘1st’ END

and add this to the Label shelf instead. Again adjust the table calculation as described above.

For the Tooltip we need to create

Tooltip Rank Suffix

IF [Rank Desc] = 1 OR [Rank Desc] = 21 THEN ‘st’
ELSEIF [Rank Desc] = 2 OR [Rank Desc] = 22 THEN ‘nd’
ELSEIF [Rank Desc] = 3 OR [Rank Desc] = 23 THEN ‘rd’
ELSE ‘th’
END

add this to the Tooltip shelf, adjust the table calculation as required. Also create

Tooltip – Note

IF ZN([Points Deducted])<>0 THEN ‘Additionally the team was deducted ‘ + STR([Points Deducted]) + ‘ points due to ‘ + [Notes] END

And then add Adjusted Points and Tooltip – Note to the Tooltip too. Adjust the Tooltip as required.

Finally adjust the look and feel by

  • Set the background colour of the worksheet to pale lilac (#f1eff6)
  • Set the mark colour to purple (#938bc1) and add a pale grey border
  • Set row and column dividers to a thin solid pale grey line (#e6e6e6)
  • Adjust the font colour of the row and column labels to black
  • Hide the Team row heading label (right click > hide field labels for rows).
  • Hide the Label Season column heading label ((right click > hide field labels for columns).
  • Hide the MIN(1.0) axis (right click pill – uncheck show header)
  • Make the mark label bold and centred
  • Set the sheet to fit width
  • Add a title
  • Name the sheet Beginner or similar

Building the Intermediate Viz

The requirement here, is we now need to highlight the teams that won and those that were relegated each season.

For this, we’re going to create another ranking field, but this time ranking in the other direction

Rand Asc

RANK([Adj Points | GD | GF], ‘asc’)

and then determine the team’s position in the season, giving consideration to the fact that in the 1994-95 season, 4 teams were relegated instead of the usual 3.

Position

IF [Rank Desc] = 1 THEN ‘C’
ELSEIF (MIN([Season End Year]) =1995 AND [Rank Asc] <=4) OR ([Rank Asc] <=3) THEN ‘R’
END

Duplicate the Beginner viz. Remove Is First from Label and add Position instead. Add Position to Colour and adjust accordingly.

Building the Advanced Viz

Now we want to extend the colouring, so the teams who were not the champions and who weren’t relegated, are coloured based on their league position.

For this duplicate the Intermediate sheet. Add Rank Desc to the Detail shelf, verifying the table calc is computing by Team only. Change it to be a discrete (blue) pill, and the change the pill so it is also on the Colour shelf, by updating the ‘detail’ icon to the left of the pill.

This will result in 2 pills on the Colour shelf and the Colour Legend listing all the permutations.

Adjust the colour legend, so all the R’s are red, the C is yellow, and the Nulls are set from automatically assigning the BuPu 3 Color Seq Palette. Use ctrl (windows) to select multiple entries in the colour legend, rather than assigning one by one.

Note if you don’t already have the BuPu 3 Color Seq Palette in your preferences file, you will need to add and then reload Tableau. For further information see here.

And that should complete all levels of the challenge. My published viz is here.

Happy vizzin’!

Donna

Binary Parameters

Donna ColesLeave a comment

This week’s challenge was a guest post by Felicia Styer, who wanted us to make multiple selections using just a single parameter, rather than any groupings or sets.

We’ll start by focusing on the initial requirement, which was to bucket into Sub-Categories selected and those not.

Setting up the parameter

The main functionality is controlled via a single string parameter which will just contain a string of 0s and 1s. 0 indicates the Sub-Category is not selected, 1 indicates it is. The position of the 1 or 0 in the string represents the associated Sub-Category. There are 17 Sub-Categories in the data set, so we need to create a parameter of 17 characters. Arbitrarily set some entries to 1 and the rest to 0.

Binary Parameter

string parameter containing the string 01000101000010000

Building the Sales by Subcategory viz

Add Sub-Category to Rows and Sales to Columns. Sort by Sales descending.

We want to assign a number for each row. We can use Index() or Rank the rows based on Sales. I chose to to the latter

Sub Cat Rank

RANK_UNIQUE(SUM(Sales), ‘desc’)

Format this to a number with 0dp but prefixed with #

Add this to Rows, change to discrete (blue pill) and then move to be listed before Sub-Category.

We want to colour the bars based on the ‘bucket’ they’re in according to the Binary Parameter.

Bucket

MID([Binary Parameter],[Sub Cat Rank],1)

This returns the character that is in the nth position in the string – ie Tables is ranked fourth, so this calculation will return the 4th character in the Binary Parameter string.

Add this to Colour and adjust accordingly. Show the Binary Parameter on the sheet, and then adjust the values between 1 and 0 to see the bars change colour.

When a bar is clicked, we want to update the parameter. We will use a dashboard parameter action to drive this functionality, but we need to pass a value into the parameter. This value needs to be a 17 character string of 1s or 0s, where only the character at the nth position based on the rank needs to differ.

For example, the string 01000101000010000 indicates Phones is selected – it’s ranked 2nd in the list and the 2nd character of the string is a 1. When Phones is clicked, we want it to become unselected. So the character in the 2nd position needs to change to a 0, while all the other characters remain the same.

Value for Param

IF [Bucket] = “0” THEN
LEFT([Binary Parameter],[Sub Cat Rank]-1) + “1” + MID([Binary Parameter],[Sub Cat Rank]+1)
ELSE
LEFT([Binary Parameter],[Sub Cat Rank]-1) + “0” + MID([Binary Parameter],[Sub Cat Rank]+1)
END

If the row is currently in Bucket 0, then get the portion of the Binary Parameter string before the nth term, concatenate it to 1 and then concatenate that with the portion of the Binary Parameter string after the nth term, otherwise, the row is associated to Bucket 1, so concatenate the preceding and following string with a 0 instead.

Add this to the Detail shelf.

Finalise the display by adding Sales to the Label, removing row & column dividers, updating the Tooltip, hiding the column headers and adjusting the font size.

Adding the selection interactivity

Add the sheet to a dashboard. Add a dashboard parameter action

Set Bucket

On selection of the sheet, set the Binary Parameter parameter passing the value of the Value for Param field. Leave the parameter with the current value when selection is cleared.

Click a bar to test the functionality. The bars should be changing colour. However, on click, Tableau automatically highlights the selected bar and the others ‘fade’. We’re already using colour to identify what’s been selected, so don’t want this to happen. To resolve this we will apply the True/False method to deselect the marks which is documented here.

You will need to create True and False calculated fields, and add them to the Detail shelf of the viz sheet. Then add a dashboard filter action as below.

Now when you click, the bars immediately change to the right colour with a single click of the mouse.

Building the Sales by Bucket bar chart

On a new sheet, add Sales to Columns, Bucket to Rows and Sub-Category to Detail. Adjust the table calculation setting of the Bucket pill so it is computing explicitly by Sub-Category.

Add Bucket to Colour and again adjust the table calculation as above. When you hover over the bar, you will see it is actually a stacked bar of each Sub-Category. We want these ordered so those with the smallest sales are on the left. Apply a Sort to the Sub-Category field on the Detail shelf to sort by Sales Descending

Widen each row. Add Sales to Label and set the Label to only show when selected (so they only appear when the segment of the bar is clicked on)

Add a Reference line to the Sales axis that shows the Sum of Sales per cell, and displays the Value. Don’t show any line or tooltip, and ensure the reference line isn’t recalculated when the bar chart is clicked.

Format the reference line label so it is positioned right middle, and adjust the font size.

Once again remove any row/column dividers and row headings and adjust the font sizes.

Arrange this chart onto the dashboard with the other chart using layout containers as required.

My version of this challenge is published here.

Bonus Challenge

For the bonus challenge to use more than 2 buckets, there was no example actually published. So I interpreted it as each click added it to the next bucket until you reached the maximum number of buckets allowed, at which point the Sub-Category would become deselected.

For this I created a parameter

# of Buckets

integer parameter from 1 to 5

The expectation in this instance was that rather than a string of 1s and 0s in the parameter, the parameter could contain any number from 0 up to # of Buckets – 1.

So the Value for Parameter field just had to change to become

IF [Bucket] = “0” THEN
//we’ve clicked once so move it to 1st bucket
LEFT([Binary Parameter],[Sub Cat Rank]-1) + “1” + MID([Binary Parameter],[Sub Cat Rank]+1)
ELSEIF [Bucket] = STR([# of Buckets]) THEN
//we’re already at the end, so reset to the starting position of 0
LEFT([Binary Parameter],[Sub Cat Rank]-1) + “0” + MID([Binary Parameter],[Sub Cat Rank]+1)
ELSE
// need to move to the next bucket
LEFT([Binary Parameter],[Sub Cat Rank]-1) + STR(INT([Bucket])+1) + MID([Binary Parameter],[Sub Cat Rank]+1)
END

The Colours associated to the Bucket field also then need to be updated to handle however many buckets you have, which you can set initially by manually updating the Binary Parameter parameter.

Note – as I have both versions in my workbook, I have fields suffixed with ‘bonus’ to represent the calculated fields/parameters needed.

My published version of this viz is here.

Happy vizzin’!

Donna

Can you display a ratio and volume on the same chart?

Donna ColesLeave a comment

This week’s #WOW2025 challenge was a guest post by Kieran Adair and had two parts to it; one to show Sales and Profit Ratio for 2024 only on the same chart, and the other to show comparisons to 2023 data on the same chart.

I built the first one way, and had to make changes in order to build the second. I could have chosen to rebuild the first chart using the fields I ended up creating for the second, but I chose not to, so this will ultimately be 2 blogs in one, as for each chart I needed different fields.

Restricting the data

We only need data for 2023 and 2024 for this challenge, so apply a data source filter (right click data source > Add data source filter) and restrict to Years 2023 and 2024 only.

Building the 2024 chart

We will be plotting Profit Ratio so need to create

Profit Ratio

SUM([Profit])/SUM([Sales])

format this to % with 0 dp.

Add Sub-Category to Rows and Profit Ratio to Columns. Add Order Date to Filter and restrict to the Year 2024 only. Sort the bars descending |(just click the icon on the toolbar)

Change the Mark type to circle and set the colour to black. Widen the rows a little.

Add Sales to Columns. Change the mark type of the sales marks card to bar. Set the colour to white and add a black border.

Adjust the Tooltip on the All marks card and verify both measures are displayed whether hovering over a circle or a bar.

Edit the Sales axis and set to custom to ensure it is fixed to start at 0 and end ‘automatic’. Adjust the axis title to include the text [Bars◻].

Make the chart dual axis BUT DO NOT synchronise axis. Remove Measure Names from the Colour shelf of the All marks card. Right click on the Sales axis at the to and Move marks to back.

Edit the title of the Profit Ratio axis so that it includes the text [Circles ●]. Remove all gridlines and zero lines and hide the Sub-Category row heading (right click the text and hide field labels for rows). Update the title and name the sheet RAVE 2024 or similar.

Building the YoY chart

For this version, we need to create some additional calculated fields

Sales – 2024

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

Sales 2023

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

Profit – 2024

IF YEAR([Order Date]) = 2024 THEN [Profit] END

Profit 2023

IF YEAR([Order Date]) = 2023 THEN [Profit] END

PR – 2024

SUM([Profit – 2024])/SUM([Sales – 2024])

format to % with 0dp

PR – 2023

SUM([Profit – 2023])/SUM([Sales – 2023])

format to % with 0dp

On a new sheet, add Sub-Category to Rows and PR – 2024 to Columns. Sort descending. Change the mark type to circle.

Drag PR – 2023 onto the PR -2024 axis and release the mouse when the 2 green column symbol appears.

This will automatically update the viz to include Measure Names and Measure Values. Adjust the Colour legend so PR 2024 is black and PR 2023 is grey, and PR 2024 is listed first (so black circles are on top when the marks overlap)

Add Sales 2024 to Columns. Change the mark type of the Sales 2024 marks card to bar. Remove Measure Names from the Colour shelf of the Sales 2024 marks card. Set the colour to white and add a black border.

Add Sales – 2023 to the Detail shelf of the Sales 2024 marks card. Right click on the Sales 2024 axis and Add Reference Line per cell referencing the Sales 2023 value as below.

Update the title of the Sales – 2024 axis to Sales [Bars◻] and set the axis to be fixed from 0 to automatic. Update the title of the other axis (Value) to Profit Ratio [Circles ●].

On the All marks card add PR 2023, PR 2024, Sales – 2023 and Sales -2024 to the Tooltip shelf and update to suit.

Make the chart Dual Axis but once again DO NOT synchronise the axis. As before move the marks (bars) of the Sales axis to the back, and remove the Sub-Category label. Remove all gridlines and zero lines and update the title.

And that’s it. I added both my sheets to dashboards, juts to improve the presentation slightly. My published viz is here.

Happy vizzin’

Donna