Yusuke set this week’s challenge focusing on stacked bar charts, and providing a different way of presenting them. This certainly took a bit of thought (and trial and error) and the hints were useful.
This is all built on one sheet!
Create field
Order Date Month
DATE(DATETRUNC(‘month’, [Order Date]))
Format to mmm yyyy and add to Columns as a discrete exact date (blue pill). Add Category to Filter and select Office Supplies. Format Sales to $ with 0 do and add to Rows. Change the mark type to bar. Add Sub-Category to Colour and adjust to suit. Widen each column. This is the basic stacked bar chart.
We need the bars sorted with the largest Sub-Category per month on the bottom of the stack. For this create a CombinedField by selecting both Order Date Month and Sub-Category in the Data pane, then right-clicking Create > Combined Field
This automatically creates a field called Order Date Month & Sub-Category. Add this field to the Detail shelf, then apply a sort to pill, to sort by sum of Sales ascending. Make sure the combined pill is displayed on the marks card above the Sub-Category field on Colour.
Add Sub-Category and Sales to the Label shelf (positioning both fields below the Sub-Category Colour pill). Align left. Then add a reference line to the Sales axis, that is set per cell to display the Sum of Sales.
Format the reference line label as required.
To build the 2nd half of the display, we need to have an axis on Columns. So double click into the Columns shelf and type MIN(0). Your display will immediately go a bit weird!
Set the view to fit height to give a bit more space. Then click on the Size shelf, and change the option from Manual to Fixed and ensure the alignment is to the left.
Edit the MIN(0) axis to be fixed from 0 to 1, and then click on the Size shelf again and add at width of 0.95 so the bars fill most of the horizontal space, but are not butted up against each other.
Now for the 2nd part of the chart. This part will require a dual axis – 1 axis to display the faded full width bars, and 1 axis to display the % of total bars.
Firstly, create an additional ‘row’ by double-clicking into the Rows and typing MIN(1). this immediately gives us the ‘background’ bars. Set the opacity on the Colour shelf to 20% and remove the 0.95 width from the Size shelf.
Create a new field
% of Total Sales
SUM([Sales]) / TOTAL(SUM([Sales]))
Format this to % with 1 dp and add to the Label shelf. Set the table calculation to be computing using Sub-Category only.
Adjust Label as required, check the box that allows labels to overlap other marks and align right. It’s all a bit squashed, so change the view from Fit Height to Standard and just manually widen the display.
Now add another instance of MIN(1) to the Rows shelf, to create another marks card. Add % Total Sales to the Size shelf on this card and remove all the fields on the Label shelf.
Make the to MIN(1) pills dual axis, and synchronise the axis. Remove Measure Names from the Colour shelf on both the MIN(1) marks cards.
Change the Category filter to both the other options and adjust the colours to suit. You will notice that the stacked marks in the bottom half will be wider with different categories. To resolve this for a cleaner display, edit the MIN(1) axis to be fixed from 0 to 10.
Finally tidy up by
hiding the right hand axis (un check show header)
Changing the title of the MIN(1) axis, and not displaying any tick marks on that axis.
Removing all gridlines & zero lines.
Adjusting font sizes of labels as required
Updating the Tooltip.
And that should be it. Just add to a dashboard and use containers to display the colour legend and filter. My published viz is here.
There’s a lot to pack into this one, so let’s get cracking.
Modelling the data
To reduce the number of calculated fields needed, and to make the build slightly easier, the data needs to be pivoted, to have a row for the Sales and a row for Profit.To do this connect to the data source, then ‘ctl-click’ to select the Sales and Profit columns, and then select Pivot
Rename the fields: Pivot Field Names -> Metric and Pivot Field Values -> Metric Value.
Creating the parameter
The weekly date selector is driven by a parameter. For the purposes of this exercise, I just chose to hardcode the weekly values. If this was being done for a real world scenario, then you’d likely use a field within in the dataset to drive the population of the parameter.
pSelectedWeek
Date parameter containing the list of relevant dates, formatted as required and defaulted to 9th Aug 2026.
Creating the Calculated Fields
There’s a lot of fields required for this build. We’ll define the majority of the core ones here:
Capture the relevant Sales/Profit value for the selected week
(note the timeframe from the previous year needs to include the equivalent week of the one selected, whereas the ‘previous’ 13 week timeframe excludes it. Hence the DATEADD functions refer to 51 weeks rather than 52. I also chose to display as -13-51 rather than -64, so it was clear how I’d shifted the dates).
Format all these to a $ with 0 dp.
We then need the difference between these values and the selected week value.
Format these to a custom number format of +”$”#,##0;-“$”#,##0
We then need to determine the standard deviation of the weekly Sales/Profit values
SD – Last 13 Weeks
{FIXED [Metric]: STDEV(
{FIXED [Metric], DATETRUNC(‘week’, [Order Date]): SUM(IF ([Order Date]>= DATEADD(‘week’, -13, [pSelectedWeek]) AND [Order Date]<[pSelectedWeek]) THEN [Metric Value] END)}
)}
This is essentially getting the sum of the sales or profit per week within the relevant timeframe, and then calculating the standard deviation of that.
Similarly we need
SD – PY Last 13 Weeks
{FIXED [Metric]: STDEV(
{FIXED [Metric], DATETRUNC(‘week’, [Order Date]): SUM(IF ([Order Date]>= DATEADD(‘week’, -13-51, [pSelectedWeek]) AND [Order Date]<DATEADD(‘week’, -51,[pSelectedWeek])) THEN [Metric Value] END)}
)}
Format these to $ with 0 dp.
If you put these all out onto a sheet along with the parameter, you can test the values you get with the solution to sense check you’re on the right path.
There’s lots more fields required, but we’ll address as we build each sheet.
Building the RAG Indicator card
On a new sheet, add Metric to Columns and manually re-order, so Sales is listed first. Then add Value – Selected Week as a discrete field to Columns (blue pill). Double click into columns shelf and manually type MIN(1) to create an axis we can then ‘build’ our cards within.
Change the mark type to Bar, and edit the axis to be fixed from 0 to 1. We need to create a field for the Rows, so create
Timeframe
IF [Order Date]>= DATEADD(‘week’, -13, [pSelectedWeek]) AND [Order Date]<[pSelectedWeek] THEN ‘Better than usual?’
ELSEIF [Order Date]>= DATEADD(‘week’, -13-51, [pSelectedWeek]) AND [Order Date]<DATEADD(‘week’, -51,[pSelectedWeek]) THEN ‘Better than last year?’
END
Add this to Rows and exclude the Null option (this will automatically add Timeframe to Filter). Re-order the row values, increase the Size of the bars to maximum and add a white border via the Colour shelf.
To colour and label the sections, we need to firstly understand what ‘state’ each metric was in, and then from this, we can then make an appropriate label.
Metric State – Last 13 Weeks
IF SUM([Value – Selected Week ]) > SUM([Value – Last 13 Week Avg]) + SUM([SD – Last 13 Weeks]) THEN ‘Above range’
ELSEIF SUM([Value – Selected Week ]) >= SUM([Value – Last 13 Week Avg]) THEN ‘Above avg’
ELSEIF SUM([Value – Selected Week ]) < SUM([Value – Last 13 Week Avg]) – SUM([SD – Last 13 Weeks]) THEN ‘Below range’
ELSE ‘Below avg’
END
Metric State – PY Last 13 Weeks
IF SUM([Value – Selected Week ]) > SUM([Value – PY Last 13 Weeks Avg]) + SUM([SD – PY Last 13 Weeks]) THEN ‘Above range’
ELSEIF SUM([Value – Selected Week ]) >= SUM([Value – PY Last 13 Weeks Avg]) THEN ‘Above avg’
ELSEIF SUM([Value – Selected Week ]) < SUM([Value – PY Last 13 Weeks Avg]) – SUM([SD – PY Last 13 Weeks]) THEN ‘Below range’
ELSE ‘Below avg’
END
We can then create
Display Response
IF MIN([Timeframe]) = ‘Better than usual?’ THEN
CASE [Metric State – Last 13 Weeks]
WHEN ‘Above range’ THEN ‘Yes ▲’
WHEN ‘Above avg’ THEN ‘Yes’
WHEN ‘Below avg’ THEN ‘No’
WHEN ‘Below range’ THEN ‘No ▼’
END
ELSEIF MIN([Timeframe]) = ‘Better than last year?’ THEN
CASE [Metric State – PY Last 13 Weeks]
WHEN ‘Above range’ THEN ‘Yes ▲’
WHEN ‘Above avg’ THEN ‘Yes’
WHEN ‘Below avg’ THEN ‘No’
WHEN ‘Below range’ THEN ‘No ▼’
END
END
Add this field to the Label and Colour shelf. Set the sheet to Fit Entire View, so you have space to see everything. The Label will need to be aligned middle centre, and the colours will need to be adjusted. You will need to select each date in turn to get all the four colour variations required.
Tidy this sheet up by removing tooltips, hiding the MIN(1) axis, removing row & column dividers, hiding row and column field labels, and formatting the Sales/Profit labels.
Building the Last 13 Week Comparison KPI card
On a new sheet, add Metric to Columns and re-order so Sales is listed first. Change the Mark type to shape and select a transparent shape.
Add Metric, Diff – to Last 13 Weeks, and Value – Last 13 Week Avg to Label. Set the sheet to Fit Entire View and align the text top left, then adjust the style and layout as required.
We need another field to show the difference from the range when the state is above or below range
Diff – to range Last 13 Weeks
IF [Metric State – Last 13 Weeks] = ‘Above range’ THEN SUM([Value – Selected Week ]) – (SUM([Value – Last 13 Week Avg]) + SUM([SD – Last 13 Weeks]))
ELSEIF [Metric State – Last 13 Weeks] = ‘Below range’ THEN SUM([Value – Selected Week ]) – (SUM([Value – Last 13 Week Avg]) – SUM([SD – Last 13 Weeks]))
ELSE NULL
END
Add this to Label and then format the field with custom formatting as follows :
“▲ Above range by $”#,##0;”▼ Below range by $”#,##0
Tidy up by hiding the header and then removing row/column dividers. Update the title.
Building the Previous Year Last 13 Week Comparison KPI card
Create a new field
Diff – to range PY Last 13 Weeks
IF [Metric State – PY Last 13 Weeks] = ‘Above range’ THEN SUM([Value – Selected Week ]) – (SUM([Value – PY Last 13 Weeks Avg]) + SUM([SD – PY Last 13 Weeks]))
ELSEIF [Metric State – PY Last 13 Weeks] = ‘Below range’ THEN SUM([Value – Selected Week ]) – (SUM([Value – PY Last 13 Weeks Avg]) – SUM([SD – PY Last 13 Weeks]))
ELSE NULL
END
Format this using the same custom formatting as described above.
Duplicate the sheet above. Replace the fields with the equivalent ‘PY’ versions, and update the title
Creating the Sales bar chart
On a new sheet, add Metric to Filter and select Sales. Add Order Date to Columns and set to the ‘week’ level as a discrete (blue) pill. Add Metric Value to Rows. Change the mark type to bar.
Create a new field
Filter – Timeframe – Bar
[Order Date]>= DATEADD(‘week’, -13, [pSelectedWeek]) AND [Order Date]<DATEADD(‘week’,1,[pSelectedWeek])
Add to the Filter shelf and select True.
We need to isolate the selected week from the other weeks, so create
Is Selected Week
WEEK([Order Date]) = WEEK([pSelectedWeek])
And add this to Columns before the Order Date field.
Create field
Colour – Last 13 Weeks
IF MIN([Is Selected Week]) THEN [Metric State – Last 13 Weeks] END
Add to Colour and adjust accordingly. Again, you’ll need to cycle through the date options to set the appropriate colours.
We want to create a average reference line, but only for the previous weeks
Bar – Ref Line
IF NOT([Is Selected Week]) THEN [Metric Value] END
Add this to the Detail shelf, then add a dotted reference line based off the average of this field
Add a further reference ‘line’, but select distribution and choose the standard deviation option. Set the colour of the band to pale grey.
Tidy up by hiding the axis, and the header pills, removing all gridlines and row/column dividers and not showing the tooltip.
Creating the Profit bar chart
Simply duplicate the above sheet and change the Metric filter from Sales to Profit.
Creating the Sales Line chart
On a new sheet, add Order Date at the week level as a discrete (blue) pill to Columns and Metric Value to Rows. Add Metric to Filter and select Sales.
Create a new field
Filter – Timeframe – Line
[Order Date]>= DATEADD(‘week’, -12, [pSelectedWeek]) AND [Order Date]<DATEADD(‘week’,1,[pSelectedWeek])
OR
[Order Date]>= DATEADD(‘week’, -13-51, [pSelectedWeek]) AND [Order Date]<DATEADD(‘week’, -51,[pSelectedWeek])
Add to the Filter shelf and select True. Add Order Date at the Year level to Colour and adjust to suit. Re-order the years so the latest year is on top.
Add Value – PY Last 13 Weeks Avg to Detail then add a reference line which uses this fiel d
to show a dashed line
Add a further reference line ‘band’ to display the Standard Deviation as before
Create a new field
This Week Value
IF DATETRUNC(‘week’, [Order Date]) = [pSelectedWeek] THEN ([Value – Selected Week ]) END
And add to Rows to create a second marks card. Change the Mark to Circle and adjust the size. Remove the fields from the marks shelf.
Crate a new field
Colour – PY Last 13 Weeks
IF MIN([Is Selected Week]) THEN [Metric State – PY Last 13 Weeks] END
Then add to the Colour shelf. Add a dark border to the mark. Once again you’ll need to change the date parameter to see all the various colour options that need to be set.
Change the aggregation of the This Week Value mark to AVG then make the chart dual axis and synchronise the axis. Hide the null indicator.
Tidy up by hiding all axis, removing all gridlines, row & column dividers and hiding the tooltip.
Create the Profit line chart by duplicating this sheet and changing the Metricfilter to Profit.
Creating the Dashboard
You need to use containers to arrange the items on the dashboard. I started with a floating vertical container positioned at 0,0 and height and width as per the requirements, then added ‘tiled’ (aka non floating) objects within. Any instances of an actual Tiled container automatically added, I removed from the dashboard. This technique gives much more control over object placement. I used horizontal containers when I needed to display the bar and line charts side by side. I just used a text field to write the chart legends, and a blank object with a coloured background to create the line dividers. Padding is your friend in this case. My object hierarchy is below
It was my turn to set the #WOW2026 Week 30 challenge, and I based it on a viz I started building some time ago, but never finalised. Given Wimbledon has recently finished, I thought it was the perfect opportunity to get this one over the line. There are multiple components to this, so I split the challenge into ‘Levels’ to help people focus on what to work on, if time was limited.
Defining the core calcs
The data provided contains 1 row per match Sinner has played in, and each match is associated to a tournament. While typically matches in a tournament are played over several days, in the dataset the Date field is the same for all matches in a single tournament, so can be used a a unique identifier for a tournament.
The ladder chart is essentially a bump chart where each round in a tournament is ‘ranked’ based on how far into the tournament the round is, where the Final is the highest rank ie 1. So the first step is to assign a ‘rank’/position to each round.
Round Position
CASE [Round] WHEN ‘F’ THEN 1 WHEN ‘SF’ THEN 2 WHEN ‘QF’ THEN 3 WHEN ‘R16’ THEN 4 WHEN ‘R32’ THEN 5 WHEN ‘R64’ THEN 6 WHEN ‘R128’ THEN 7 WHEN ‘Q3’ THEN 8 WHEN ‘Q2’ THEN 9 WHEN ‘Q1’ THEN 10 WHEN ‘RR’ THEN 11 END
We then need to be able to identify the best round achieved in the tournament
Best Round per Date
{FIXED [Date]: MIN([Round Position])}
We also need to identify the Grand Slam tournaments from other tournaments. I’ve used a set for this. Right click on Tournament > create > set
Is Grand Slam
select the 4 entries : Australian Open, Roland Garros, Wimbledon, US Open
I’ve also created a parameter
pGrandSlamsOnly
boolean parameter, defaulted to True, with the True option aliased as Grand Slams Only and False aliased as All Tournaments
I then created
Filter – Matches
([pGrandSlamsOnly] AND [Is Grand Slam]) OR NOT([pGrandSlamsOnly])
This will return true if either pGrandSlamsOnly = Grand Slams Only (ie is True) AND the tournament is a member of the Is Grand Slam set, or pGrandSlamsOnly = All Tournaments (ie is False)
To ensure only the best round per tournament is shown, I also created
Keep Best Round Only
[Round Position] = [Best Round per Date]
Finally, we need to identify if Sinner was the winner of the match or not. We can determine this by looking for the name Sinner in the text of the Match field which precedes the string ‘d. ‘ . (I assume d. stands for ‘defeated’).
Is Winner
CONTAINS(TRIM(SPLIT([Match], “d.”, 1 )),”Sinner”)
Building the ladder chart
On a new sheet, add Round to Filter and exclude RR. Add Keep Best Round Only to Filter and set to True. Add Filter – Matches to Filter and set to True. Then finally add Surface to Filter and select all options.
Add Date to Columns as a continuous (greed) exact date, and add Round Position as a continuous dimension (unaggregated green pill) to Rows. Edit the y-axis and reverse the axis. Change the mark type to a line and set the path to be a stepped line.
Add Tournament, Rank, Date, Surface and Round to the Tooltip shelf. Adjust the number format of the Rank field so it is a number with 0dp but prefixed with #. Adjust the format of the Date field on the Tooltip to be mmmm yyyy (ie August 2019 format).
Additionally, create a field
Label:Unranked
IIF(ISNULL([Rank]),’Unranked’,NULL)
and add this to Tooltip too, the adjust tooltip as required. Reduce the Size of the line and adjust the colour (I used #7ed0d9). Set the background colour of the worksheet too (I used #f7fcf5)
Add another instance of Round Position to the Rows to make a 2nd marks card. Set the mark type to Shape and add Is Winner to Shape. Increase the mark size. Set shapes accordingly. Add Surface to Colour and adjust to suit (I used colours from the hue circle palette, reduced to 60% opacity). Then make the chart dual axis and synchronise axis.
Add Is Winner to the Size shelf of the 2nd marks card, and adjust sizes to suit too, so the Star is at least as big as the other mark.
Remove all row/column dividers, gridlines, zero lines, axis lines etc.
To make the labelled ‘ladder rungs’, we’re simply adding multiple constant reference lines. Right click the left hand y-axis > add reference line. Add a constant at 10, labelled Q1 with a blue dotted line.
Then format the label so it is aligned in the middle of the line (I reduced font to 8pt) and removed any shading
Then repeat 9 more times for each ‘rung’ 🙂
At the end, hide both the axis (uncheck show header) and remove the x-axis title. Fix the x-axis from 01 Aug 2017 to 01 Jun 2027. The test the filters work as expected.
Building the Viz in Tooltip
On a new sheet, add Date as a discrete exact date (blue pill), Round, Match to Rows. Go back to the Ladder chart and set the Round, Filter – Matches and Surface filters to apply to this worksheet too. Apply a sort to the Round pill, to sort by the Round Position field.
Add Score to the Text shelf. Then double click into Columns and type the text ‘Result’, then right click the ‘Result’ text and hide labels for columns.
Go back to the Ladder chart and update the Tooltip – insert the VIT sheet, and adjust the size /width as required and apply filters to Date & Tournament.
On hover, the list of matches should now display
Building the KPIs
We need a few fields for this
Tournaments
COUNTD([Date])
Matches
COUNT([Sinner])
Note – this is just referring to the automatic sheet row count field
Wins
IIF([Is Winner?],1,0)
Titles
IIF([Is Winner?] AND [Keep Best Round Only],1,0)
Grand Slams
IIF([Is Grand Slam] AND [Is Winner?] AND [Keep Best Round Only],1,0)
On a new sheet, Add Measure Names to Columns and Measure Values to Rows. Remove the measures that aren’t required. Apply the same ‘shared’ filters to this sheet.
Change the mark type to a shape and use a transparent shape. Add Measure Names to Label and arrange labels middle centre, and format as required. Set the worksheet background to match the ladder viz.
Building the strip plot
Create a new field
Within Top 10
[Rank]<=10
On a new sheet, add this to Filter and set to True. Then apply the other 3 shared filters to this sheet.
Add Date to Columns as a continuous exact date (green pill). Fix the axis to the same dates as before 01 Aug 2017 to 01 Jun 2027. Set the Mark type to Circle and add Rank to colour, reversing the range as required so the lower number is darker. Set the circle to have a border and 80% opacity. Add Date, Surface, Tournament and Rank to Tooltip and adjust as required. Remove all gridlines, row/column dividers, gridlines etc and set background colour as before.
The double click into Rows and type MIN(0) to create a y-axis. Then add a 0 constant reference line (as before) but label this Within Top 10. The hide both the x and y-axes.
Building the dashboard
Now you have all the components, they just need to be added to a dashboard. Use layout containers to help with this. To ensure the ladder chart & the strip plot align, arrange these in a vertical container. Both objects should be set to fit entire view and any padding to the left or right of the objects should be identical.
This week’s challenge was a guest post by Lisa Hitch to generate a pareto chart, but adding visual clues via background colours and lines to highlight the key areas of focus.
Ultimately, this was a relatively simple table calculation challenge, requiring just 2 calculations.
Just to see what these are doing, add Customer ID into Rows and Sales onto Text. Sort by Sales descending (this is a key feature in getting the pareto chape we need).
Create another field
cust count
COUNTD([Customer ID])
and add this into the table (we ultimately don’t need this field, but just creating so it’s clear what’s happening.
Add a Running Total quick table calculation to the cust count field – the field will now show a cumulative total down the table
Also create a field
total cust
TOTAL(COUNTD([Customer ID]))
Add this to the table too – every row reports the same – the total number of distinct customers int he data set.
Now adding Running Sum % of Customers into the table, you can see that the result of this field is a calculation of cust count and total cust. We could have broken it down like this, but I chose to just use a single calculation.
We could apply similar principals to the Sales field so you can see how this is derived, but hopefully you get the gist. Ultimatel, the data we need is below
On a new sheet, add Running Sum % of Sales to Rows, Running Sum % of Customers to Columns and Customer ID to Detail. Adjust both the table calculation to be explicitly computing by Customer Id and apply a sort on the Customer ID pill, to be sorted by Sales descending.
Change the mark type to Area and adjust the colour and set opacity to 100%.
To set the background colours, we’ll used fixed reference bands.
Right click the X-axis and Add Reference Line. Select Band and then set a constant from 0 to 0.1 and colour as required.
Repeat the process 4 more times, creating bands for 01-0.2, 0.2-0.8, 0.8-0.9 and 0.9-1.
The add reference lines to provide the 20% & 80% intersection lines. This time, select the Line option and again use constants, but rather than fill, select a thick white line
Again repeat to create a line at 0.8 on the X-axis. Then add reference lines to the Y-axis at the same intervals.
Add an annotation to the point where the X-axis = 20%. Use the tooltip to find the exact point where % of customers = 20% (this can be a bit fiddly), then right click and Annotate > Mark. Edit the annotation as required, then format the resulting text box as required. Repeat for the point at which the Y-axis = 80%.
Finish off by hiding all tooltips, removing all gridlines & row/column dividers, and fixing both axes from 0-1. Then add to a dashboard and you’re done.
For this week’s challenge, Yoshi got us creating gauge charts using Tableau’s radial viz extension.
Modelling the data
Yoshi provided a version of the Superstore dataset along with a Budget csv. After downloading, I related the 2 files within Desktop using the following relationships pictured below
Building the gauge chart
On a sheet, select the Add an extension option from the mark type dropdown and then select the Radial (by Tableau) option, and click Open on the resulting screen
Add Measure Names to Ring and Measure Values to Angle and then add Measure Names to filter and retain the Sales and Budget options only.
Click Format Extension and then set the options as follows
Total angle (degrees) : 180
Starting angle (degrees) : 270
Ring padding : 30
Segment padding : 5
Segment labels : Ring and Angle Values
Font : size 9
Centre size (%) : 50
Show centre label: on
Automatic font size: on
Edit colours and select appropriate colours
Adjust the order of the Sales and Budget pills in the Measure values pane if required
Add Region, Segment, Category and Order Date at the month-year level to the Filter shelf. Set Category to Furniture and Order Date to December 2025.
Format the Measure Values pill on the Angle shelf so that it displays the numbers as $ with 0 dp.
Create a new field
Acheivement %
(SUM([Sales])/SUM([Budget])) / 2
format this to % with 1 dp and add to the Centre shelf. Note, the division by 2 is necessary due to their being 2 measures (and therefore 2 marks) displayed and the number duplicating itself.
Create another field
Tooltip – Achievement %
(SUM([Sales])/SUM([Budget]))
Format this to % with 1 dp and add to the Tooltip shelf along with Sales, Budget and Category. Update the Tooltip to suit. Edit the title to reference the Category field, and then name this sheet Gauge – Furniture or similar.
Then duplicate the sheet. Change the Category filter to Office Supplies and name the sheet Gauge – Office. Repeat to create a version for Technology. So you should end up with 3 sheets, one for each Category. Apply the Region, Segment and Order Date filters to be shared across all 3 workbooks.
Building the bar chart
Create a field called
Sales Rank
RANK(SUM([Sales]))
and change it to be discrete (right click > convert to discrete)
Add Category to Columns and Sales Rank to Rows and Sub-Category to Detail. Set the table calculation associated to Sales Rank to be computing by Sub-Category only.
Double click into Columns and manually type MIN(1) to create a ‘fake axis’. Change the mark type to bar and edit the axis to be fixed from 0 to 1. Widen each row slightly. Then move Sub-Category to Label
Then via the Colour shelf, reduce the opacity to 0% and remove the border
Now add Sales to Columns. and on the Sales marks card, move Sub-Category back to Detail, and rest the opacity to 100%
Colour the bars as required. Add Budget to the Detail shelf, then add a reference line to the Sales axis, which shows the Budget per cell as a line with a fill below of light grey.
From one the gauge sheets, set the Region, Segment and Order Date filters to also apply to this sheet.
Add Achievement % to Tooltip and adjust the Tooltip on the Sales marks card only. Remove all the text from the Tooltip on the MIN(1) marks card.
Then format the sheet by
editing Sales axis and removing the axis title
editing the MIN(1)axis and removing the title and tickmarks
hide the sales Rank header (right click pill > uncheck show header
hide the Category header
remove row & column dividers
Then add the Gauge sheets into a horizontal container on a dashboard, setting the container to ‘distribute contents evenly’. Add the bar chart underneath, but I ‘floated’ it into position as each gauge chart object takes up more vertical space than necessary (the size of the object doesn’t adapt to the fact you’re only showing half a circle).
For this week’s challenge, Yusuke asked us to provide a solution to allow charts to be coloured by different dimension, but he sprinkled a few extras in just for good measure 🙂
Defining the parameter
The key driver here is going to be the use of a parameter to define the dimension we need to colour by.
pColourBy
string parameter defaulted to Order Date, listing the 4 options as below
We then need a field that uses this parameter to define the actual dimension we’ll colour by
Colour
CASE [pColourBy] WHEN ‘Order Date’ THEN STR(YEAR([Order Date])) WHEN ‘Region’ THEN [Region] WHEN ‘Category’ THEN [Category] WHEN ‘Segment’ THEN [Segment] END
Building the Order Date chart
On a new sheet, add Order Date to Columns and Sales to Rows. Change the mark type to Bar and add Colour to the Colour shelf. Adjust the colours to suit, set the opacity to 70% and add a white border. Show the pColourBy parameter.
Change the options in the pColourBy parameter and each time readjust the colours as you wish.
Add a reference line to the Sales axis that displays the value of TotalSales per cell
Format the reference line to format the displayed number in $M and bold font, and align top middle.
Create a new field
% of Sales
IF SUM([Sales]) / TOTAL(SUM([Sales])) <> 1 THEN SUM([Sales]) / TOTAL(SUM([Sales])) END
and format to % to 1dp. This will only display a value if its not 100%.
Add this to the Label. Adjust the table calculation setting so it is computing by the Colour field only.
Adjust the Label so the font is bold and the label only appears when Highlighted. Then update the Tooltip as required.
Although not explicitly called out in the requirements, I noted that if Yusuke clicked on the chart title, it reset the dimension to colour by. To deal with this we need to create
param Order Date
‘Order Date’
Add this to the Detail shelf.
We also need to ‘fake’ the title to be part of the chart itself (so it’s clickable). Double click into the Columns and manually type ‘Sales by Order Date’ and position the pill created before Order Date.
Right click on the column label (the text in darker font) and hide field labels for columns. Then right click on the column label to format – set the font to 12pt and bold, align left and shade the background to light grey. Increase the width of the column heading.
Then right click on the corner whitespace next to the heading just created, and format. Apply a light grey shading to the corner too.
If the ‘title’ is clicked, we don’t want it to be ‘highlighted’/’selected’. For this we will need fields
True
TRUE
False
FALSE
Add both of these to the Detail shelf.
Finally tidy up by removing the axis title, adjusting the font of the axis labels (I made them a bit darker), and removing row & column dividers. Name the sheet Order Date or similar.
Building the Region chart
Duplicate the Order Date chart and then click the option in the menu to swap axis so we have a horizontal bar chart.
Move the ‘Sales by Order Date’ pill from Rows to Columns and update the text to become ‘Sales by Region’ instead. Drag the Region pill and drop it directly over the Order Date pill on the Rows so it replaces it and all references to the field are replaced too. Widen the rows.
Right click on the ‘Region’ text in the column heading and hide field labels for rows. Format the reference line to align middle right.
Create a new field
param Region
‘Region’
and add this to the Detail shelf instead of the param Order Date field. Name the sheet Region or similar
Building the Category Chart
Duplicate the Region chart, and go through similar steps described above so the ‘title’ is Sales by Category and a new field
param Category
‘Category’
replaces param Region on the Detail shelf.
Building the Segment Chart
Repeat as above, this time setting the ‘title’ to Sales by Segment and a new field
param Segment
‘Segment’
replaces param Region on the Detail shelf.
Adding the interactivity
Add the sheets to a dashboard using layout containers and padding to organise as required. Then create the following dashboard actions
Highlight Action :Highlight
On hover of any of the charts on the dashboard, target all other charts, highlighting based on the Colour field only.
This action makes all the % labels appear when the mouse cursor is moved over the bars.
Parameter Action : Set Order Date Param
On Select of the Order Date sheet, set the pColourBy parameter with the value from the param Order Date field.
Parameter Action : Set Region Param
On Select of the Region sheet, set the pColourBy parameter with the value from the param Region field.
Parameter Action : Set Category Param
On Select of the Category sheet, set the pColourBy parameter with the value from the param Category field.
Parameter Action : Set Segment Param
On Select of the Segment sheet, set the pColourBy parameter with the value from the param Segment field.
These actions change the value displayed in the pColourBy parameter when the ‘title’ of the charts is clicked on.
Filter Action: Deselect Order Date Title
On select of the Order Date sheet on the dashboard, target the Order Date worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.
Filter Action: Deselect Region Title
On select of the Region sheet on the dashboard, target the Region worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.
Filter Action: Deselect Category Title
On select of the Category sheet on the dashboard, target the Categoryworksheet directly, passing the selected values of True = False. Show all values when selection is cleared.
Filter Action: Deselect Segment Title
On select of the Segment sheet on the dashboard, target the Segment worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.
And once these have all been applied, you should have a functioning dashboard. My published version is here.
After connecting to the dataset, add Sales to Columns and Profit to Rows. Add Customer Name to Detail and change the mark type to circle.
The chart is divided by reference lines which I chose to define as parameters (but these were additional to the ones mentioned in the challenge requirements).
pProfitRef
integer parameter defaulted to 0
pSalesRef
integer parameter defaulted to 5000
Add both parameters to the Detail shelf, the add a reference line on the Sales axis that refers to the pSalesRef value.
and then repeat and add a reference line to the Profit axis referencing the pProfitRef field.
To colour the 4 segments of the chart, create new fields
Sales per Customer
{FIXED [Customer Name]:SUM([Sales])}
and
Profit per Customer
{FIXED [Customer Name]: SUM([Profit])}
These capture the total sales / profit at the customer level, and we can then determine which quadrant each customer is in by
Cohort
IF [Sales per Customer]>=[pSalesRef] THEN IF [Profit Per Customer]>=[pProfitRef] THEN ‘High Sales, High Profit’ ELSE ‘High Sales, Low Profit’ END ELSE IF [Profit Per Customer]>=[pProfitRef] THEN ‘Low Sales, High Profit’ ELSE ‘Low Sales, Low Profit’ END END
Add this to the Colour shelf and adjust colours to suit. Then set the opacity to 50% and increase the size of the marks a bit.
To add matching coloured borders around the marks, add another instance of Profit to Rows. Change the mark type of the 2nd Profit marks card to Shape and change the shape to be an open circle. Set the opacity to 100%. Set the chart to dual axis and synchronise the axis.
Adjust the Tooltip and hide the right hand axis (uncheck show header).
Dynamically adjust the axis
The axes will change by adjusting them to refer to parameters. By default we need the axis to try to replicate what it gets set to automatically. For this we need to capture the maximum and minimum sales and profit values and add a ‘buffer’ to give the extra space. I played around with a few options for the buffer, so created a parameter to store this until I got the value that seemed to work best (again this was an additional parameter that I added to just help not having to change multiple calculated fields as I got the value I wanted.)
pBuffer
integer parameter defaulted to 2000
Then create 4 fields to define the max & min of the two measures +/- buffer
Min Sales + Buffer
{MIN([Sales per Customer]) – [pBuffer]}
Max Sales + Buffer
{MAX([Sales per Customer]) + [pBuffer]}
Min Profit + Buffer
{MIN([Profit Per Customer]) – [pBuffer]}
Max Profit + Buffer
{MAX([Profit Per Customer]) – [pBuffer]}
Then create 4 parameters which will define the values we ca use to set the axis
pX-Min
float parameter that is set to the Min Sales + Buffer field when workbook opens (selecting this will then populate the value)
Create further parameters
pX-Max
float parameter that is set to the Max Sales + Buffer field when workbook opens
pY-Min
float parameter that is set to the Min Profit + Buffer field when workbook opens
pY-Max
float parameter that is set to the Max Profit + Buffer field when workbook opens
Once all the parameters exist, edit the Sales Axis and change the axis to use a custom range that references the pX-Min and pX-Max parameters
Do the same for the Profit axis, but reference the pY-Min and pY-Max parameters instead.
Finally while we’re still on the workbook, create two new fields
True
TRUE
and
False
FALSE
and add these to the Detail shelf. We’ll need these later to stop marks highlighting when we click.
Name the sheet Scatter or similar.
Building the Reset button
This actually requires another sheet (even through the requirements says 1 sheet).
Create a new field
Reset Axis
‘Reset Axis’
Add this to the Text shelf of a new sheet. Change the mark type to shape and select a transparent shape (refer to this blog to understand how to create this).
Set the view to Entire View and align the font middle centre and increase the font size. Set the background of the whole worksheet to black. Adjust the tooltip. Add Min Sales + Buffer, Max Sales + Buffer, Min Profit + Buffer and Max Profit + Buffer to the Detail shelf, along with the True and False fields.
Name the sheet Reset or similar
Adding the interactivity
Add the Scatter and Reset sheets to a dashboard, removing any parameters/legends etc that get added. Create dashboard parameter actions to set the axis parameters when selections are made on the scatter plot:
Set X Max
On select of the Scatter sheet, set the pX-Max parameter, passing in the maximum value of the Sales field.
Set X Min
On select of the Scatter sheet, set the pX-Min parameter, passing in the minimum value of the Sales field.
Set Y Min
On select of the Scatter sheet, set the pY-Min parameter, passing in the minimum value of the Profit field.
Set Y Max
On select of the Scatter sheet, set the pY-Max parameter, passing in the maximum value of the Profit field.
Also create dashboard parameter actions to ‘reset’ the axis parameters when the Reset button is clicked:
Reset X Min
On select of the Reset sheet, set the pX-Min parameter, passing in the minimum value of the Min Sales + Buffer field.
Reset X Max
On select of the Reset sheet, set the pX-Max parameter, passing in the maximum value of the Max Sales + Buffer field.
Reset Y Min
On select of the Reset sheet, set the pY-Min parameter, passing in the minimum value of the Min Profit + Buffer field.
Reset Y Max
On select of the Reset sheet, set the pY-Max parameter, passing in the maximum value of the Max Profit + Buffer field.
All these 8 actions should now combine to drive the ‘zoom & reset’ functionality.
Finally, the last step to make the display ‘nicer’ is to deselect the marks from being highlighted when selected. Add a dashboard filter action
Deselect Scatter
on select of the scatter object on the dashboard, target the scatter sheet directly, passing the selected fields of True = False. Show all values when the selection is cleared.
Repeat an create a similar action for Deselect Reset.
Erica collaborated with Giulio D’Errico for this week’s #WOW2025 challenge, which contained lots of features, though the main challenge was to display filter on Region, which along with the Region name also displayed a KPI indicator that could change based on selection from other parameters.
Defining the parameters
We need 3 parameters for this challenge
pMeasure
strig parameter that lists the two options Profit and Sales; defaulted to Profit.
pProfitThreshold
integer parameter that lists the specified values, defaulted to 2,000
pSalesThreshold
integer parameter that lists the specified values, defaulted to 30,000
Building the core scatter plot
Add Sales to Columns and Profit to Rows and Sub-Category to Detail. Show the 3 parameters.
When pMeasure = Profit, we need to display horizontal reference lines against the Profit axis, and when Sales is selected we need to display vertical reference lines against the Sales axis. We need the following fields to return the user defined thresholds:
Ref – Profit Threshold
IF [pMeasure] = ‘Profit’ THEN [pProfitThreshold] END
Ref – Sales Threshold
IF [pMeasure] = ‘Sales’ THEN [pSalesThreshold] END
We also need to define the average per measure for each region:
IF [pMeasure] = ‘Profit’ THEN [Profit Avg Per Region] END
Ref – Sales Avg
IF [pMeasure] = ‘Sales’ THEN [Sales Avg Per Region] END
Add the 4 Ref – XXX fields to the Detail shelf. Then add 2 reference lines to the Sales axis; one referencing Ref – Sales Avg (coloured as a purple dashed line at 100% opacity) and one referencing Ref – Sales Threshold (coloured as a black dashed line at 50% opacity). Display a custom label and then format the label to be aligned vertically and coloured based on the relevant line, and with a 0% shading. Set the pMeasure to Sales to make these display.
Then repeat, adding 2 reference lines to the Profit axis instead. Change pMeasure to Profit for these to appear.
Change the mark type to square. Each mark needs to be coloured whether it is above or below the average for the measure selected.
Colour
IF [pMeasure] = ‘Profit’ THEN IIF(SUM([Profit]) >= SUM([Profit Avg Per Region]),1,0) ELSE IIF(SUM([Sales]) >= SUM([Sales Avg Per Region]),1,0) END
Set this to be discrete and then add to the Colour shelf and adjust accordingly.
Creating the colour-coded filter
The viz needs to be filtered by Region, but the filter displayed needs to show the region name along with an indicator based on whether the average for the region is above the threshold or not. This took a bit of an effort, but I finally managed it.
We need a field to capture the ‘KPI indicator’ based on which measure was selected. It also needs to be computed per region. We need a FIXED LoD for this, and I made use of coloured unicode characters from this site.: https://unicode-explorer.com/ (large yellow circle and large green circle which you can just ‘copy and paste’ into the calculation)
Region Indicator
{FIXED [Region]: (
IF [pMeasure] = ‘Profit’ THEN IIF(SUM([Profit Avg Per Region])>=[pProfitThreshold],’🟢’,’🟡’) ELSE IIF(SUM([Sales Avg Per Region])>=[pSalesThreshold],’🟢’,’🟡’) END )}
I then created
Filter – Region
[Region Indicator] + ‘ ‘ + [Region]
Add this to the Filter shelf, select all values and show the filter. If you change the pProfitMeasure to Profit and pProfitThreshold to 6,000, you’ll see the KPI indicators change.
Format the Tooltip
The tooltip requires several calculated fields to be created. Certain fields only need a value based on the pMeasure and others have conditional formatting (different colours) applied. For the tooltip, I created all these fields:
Add all these fields to the Tooltip. Update the Tooltip to reference the fields and the pMeasure parameter, colouring the text as required. Some of the fields will only display based on the filters selected, so they get places side by side with no spacing.
Finalise the viz by updating the title to reference the pMeasure and the Label – Region field. Remove all gridlines, axis rulers, zero lines etc. Colour the background of the sheet to pale blue.
Building the Overall Indicator
For the bonus challenge, we need to display an indicator that is green if all the regions are green and yellow if at least 1 region is yellow.
On a new sheet, add Region and Region Indicator to Rows and show the 3 parameters.
We’re going to create a field that will return 1 if the Region Indicator is yellow and 0 otherwise.
Region Indicator – Is Below
IIF( [Region Indicator] = ‘🟡’,1,0)
Set to be discrete and add to Rows. Change the pProfitThreshold to 6000 to see the flag change.
Create
Overall Region Indicator
IIF(WINDOW_MAX(MAX([Region Indicator – Is Below]))=1,’🟡’, ‘🟢’)
This says, if the maximum value of the rows ‘in the window’ is 1, then display a yellow indicator, else green. Add to Rows, and adjust the pProfitThreshold to see the behaviour.
Create a field
Filter – Index = 1
INDEX() = 1
Add to Filter shelf and set to True
Move Region to Detail. Remove Region Indicator and Region Indicator – Is Below. Adjust the table calculation setting of Overall Region Indicator to compute using Region only, and do the same for the tableau calculation on Filter – Index = 1 (re-edit the filter after changing, so only True is selected).
Note – originally I used a transparent shape mark type and displayed the indicator as a label, but after publishing to Tableau Public, the indicator became distorted, so I adjusted how this was built.
Set the mark type to circle and add Overall Region Indicator to Colour. Adjust to be green or yellow, depending on the colour of the KPI indicator (you’ll need to change the pProfitThreshold value to ensure you set the colour for both the yellow and green options).
Finally create
Tooltip – Overall Indicator
IIF([Overall Region Indicator]=’🟢’,’All regions meet the ‘ + [pMeasure] + ‘ target’, ‘At least one region does NOT meet the ‘ + [pMeasure] + ‘ target’)
Add this to Tooltip and then set the background colour to pale blue.
Building the dashboard & adding dynamic zone visibility
Using layout containers, add the viz to a dashboard. Use a horizontal layout container to arrange the parameters, the Region filter and the Overall Indicator sheet. I used a floating text object to display the ‘legend key’ again copying come unicode characters from the website referenced earlier.
Then create 2 new boolean calculated fields
Is Profit
[pMeasure] = ‘Profit’
Is Sales
[pMeasure] = ‘Sales’
Select the Sales Threshold parameter object, then update the visibility so it only displays if Is Sales is true (via the Layout > Control visibility using value option)
Repeat the same for the Profit Threshold object, but reference the Is Profit field instead.
Now as you switch the measure between Sales and Profit, the relevant threshold parameter will display. My published viz is here.
Erica set this fun and incredibly useful challenge this week, based on the TC25 talk by Lorna Brown & Robbin Vernooij, to showcase different methods of normalising data when comparing measures which have drastically different scales.
Building the Raw Values chart
Add Sales to Rows. Then drag Quantity on to the canvas and drop the pill on the Sales axis (when you see the ‘2 column’ icon appear). This has the affect of adding the fields onto a shared axis, and the sheet will update to automatically reference Measure Names and Measure Values. Swap Quantity so it is displayed below Sales in the Measure Values section.
Add Region and Category to Detail and change the Mark type to Circle.
I’m going to incorporate the last requirement at this stage, as it helps with the build, so create parameters
pSelectedRegion
string parameter, defaulted to West
pSelectedCategory
string parameter, defaulted to Furntiture
show both these parameters on the sheet.
Create a new field
Is Selected Region & Category
[pSelectedCategory]=[Category] AND [pSelectedRegion]=[Region]
Add this field to Colour, and swap the values in the legend, so True is listed first. Then change the Region on the Detail shelf, so it is also on colour, by adjusting the icon to the left of the pill. Adjust the colours as required and then reduce the opacity on colour to 80%.
Manually update the entry in the pSelectedRegion parameter to each Region, so the True-<Region> colour combination can be updated to the dark grey.
Add Is Selected Region & Category to Size. Edit the size so they are reversed and the range in size is closer than the default. Once done, then manually adjust the dial on the Size shelf.
Show mark labels, selecting the option to only show the min & max values per cell and aligning middle right
Update the Tooltip. Then create fields True = TRUE and False = FALSE and add both of these to the Detail shelf. We’ll need these to disable the default highlighting later (adding now, as for all the other sheets, we’ll duplicate this one, so makes things easier).
Show the caption (Worksheet menu > show caption) and update the caption to reference the website Erica refers to. Then update the title of the sheet, and name the tab Raw or similar.
Building the Decimal Normalisation chart
Duplicate the Raw sheet, and name Decimal or similar. Update the title.
Create new fields
Sales – Decimal
SUM([Sales]) / 10^6
Quantity– Decimal
SUM([Quantity]) / 10^4
Drag Sales – Decimal onto the canvas and drop directly over the existing Sales pill in the Measure Values section, so it replaces it. Do the same with the Quantity – Decimal pill. Uncheck Show Labels.
Add constantreference line of 0 that displays as a black solid line at 100% opacity
Repeat and create a constant reference line with value of 1. Edit the axis and fix from -0.05 to 1.05 and remove the axis title.
Update the text in the caption.
Building the Max-Min Normalisation Chart
Duplicate the Decimal sheet and rename Max-Min or similar. Update the title.
Drag Sales – Max-Min onto the canvas and drop directly over the existing Sales – Decimal pill in the Measure Values section, so it replaces it. Do the same with the Quantity – Max-Min pill.
Adjust the table calculation setting for each of the measures so they are computing by Category, Region and Is Selected Region & Category.
Adjust the Tooltip if required. Right click on the bottom column headings and Edit Alias to update the text- you may not be able to rename Sales – Max-Min along xyz… just to ‘Sales’, so you may need to be creative and add spaces eg ‘ Sales ‘ or similar. Update the caption.
Building the Z-Score Normalisation Chart
Duplicate the Max-Min sheet, and name Z-Score or similar. Update the title.
Drag Sales – Z-Score onto the canvas and drop directly over the existing Sales – Max-Min pill in the Measure Values section, so it replaces it. Do the same with the Quantity – Z-Score pill.
Adjust the table calculation setting for each of the measures so they are computing by Category, Region and Is Selected Region & Category. Remove the reference line for the constant value of 1. Edit the axis, so the range is now Automatic rather than fixed.
As before, adjust the Tooltip again if required, edit the column labels using the alias feature, and update the caption.
Creating the dashboard and adding the interactivity
Add all 4 charts onto a dashboard, using a horizontal container to arrange the charts side by side. From the object context menu on the dashboard, select the option to show the caption
To disable the default highlighting ‘on click’ create a dashboard filter action based on the True/False method described here – you’ll need to create an action per sheet.
To set the parameters, create a parameter action
Set Category
On select of all the sheets, set the pSelectedCategory parameter passing in the value from the Category field.
Create another similar action called Set Region which sets the pSelectedRegion parameter with the value from the region field.
Finally, add a text section to the top right of the dashboard that references the pSelectedRegion and pSelectedCategory parameters.
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.
