Lorna set this week’s #WOW2025 challenge asking us to recreate what looked to be a simple line and area chart combination. I have to admit this was indeed a bit tricksy. I built out the data relatively quickly, but when trying to create the area chart I just wasn’t getting any joy.
After lots of trial and error, attempting various options, I eventually resorted to checking out Lorna’s solution. I found there were two specific areas that were causing the creation of the area chart to fail..why this happens is still a mystery….
Before we get on with the solution, I figured it was worth detailing at the start what I tried that failed, just in case you’ve been banging your head against a brick wall too 🙂
What didn’t work
The solution requires table calculations; for one calculation we need a percent of sales per quarter which means we also need to work out the total sales for the quarter. I originally used a FIXED LOD for this but found I could only get the area chart to work if I used a WINDOW_SUM() table calculation instead.
This challenge also requires all the customers for each quarter to be in the view to identify which are in the top X. I used a sorted INDEX() table calculation to identify the top X, but this too failed to work when it came to actually creating the area chart. Using the RANK() table calculation instead worked.
Like I said before why? I don’t know… but putting down as one of ‘just those things’ and hoping that maybe I’ll remember this post in future if it happens again!
Now back to the solution guide…
Creating the calculations
We’re dealing with table calculations, so I’m going to build out the required data in a tabular format to start with.
Create a parameter
pTop
Integer parameter ranging from 5 to 20 with the step size of 5 defaulted to 20
Show this parameter on the sheet, and then create a calculated field
Order Date (Quarters)
DATE(DATETRUNC(‘quarter’, [Order Date]))
Format to the YYYY QX style. Add Order Date (Quarters) to Rows as a discrete exact date (blue pill). Add Customer Name to Rows and add Sales to Text.
We need to identify customers who are in the top x for each quarter. Create a new calculated field
Is Top X Customer?
RANK(SUM([Sales]))<=[pTop]
Add to Rows and adjust the table calculation so it is computing by Customer Name only.
We now want the Sales just for those customers who are in the top x, so create
Top X Sales
IF [Is Top X Customer?] THEN SUM([Sales]) END
Add this field to Rows and we should see that Sales are only displayed for those customers that where Is Top X Customer? = True.
We now need the total of these sales per quarter so create
Total Top X Sales
WINDOW_SUM([Top X Sales])
Add this to the table and adjust the table calculation so both nested calculations are computing by just Customer Name. You should see that the total value is the same for every row for each quarter
We also need the value of the total sales in the quarter regardless of whether the customer is in the top X or not so create
Total Sales per Quarter
WINDOW_SUM(SUM([Sales]))
Add this to the table and again adjust the table calculation to compute by Customer Name only.
Now we have these two figures we can calculate the percentage. Create a new calculated field
Sales % per Quarter
[Total Top X Sales]/[Total Sales per Quarter]
Format this as a % to 1dp. Add to the table.
Duplicate this sheet. I like to do this as I make further changes so I don’t want to lose what I had.
Remove all the surplus field so that only Order Date (Quarters), Customer Name and the Sales % per Quarter fields remain
Create a new field
Customer Index
INDEX()
Convert this field to discrete (right click on the field).
Add this field to the Filter shelf and select 1. Adjust the table calculation so that it is computing using Customer Name only and sorted by SUM of Sales
Re-edit the filter and reselect 1 (making changes to the table calculation resets the filter values).
If everything has been set properly then you should end up with 1 row per quarter. Move the Customer Name pill from Rows to Detail.
We’ve now got the core fields we need to build the viz.
Building the Viz
Once again start by duplicating this sheet. Move Order Date (Quarters) to Columns and change to be continuous (green pill), then move Sales % per Quarter to Rows.
Add another instance of Order Date (Quarters) to the path shelf and change to be an attribute. You should now have a line chart. Increase the size of the line a little.
Add another instance of Sales % per Quarter to Rows, making sure the table calculation is set exactly as the existing version. Change the mark type of the 2nd marks card to Area.
Make the chart dual axes and synchronise axes .
Finally, tidy up the chart by
Adjusting the Tooltip
Removing gridlines, zero lines and row/column dividers
Hide the right hand axis
Fix the left hand axis to end at 1 (so the axis goes to 100%)
Edit the left hand axis title
Update the sheet title to reference the pTop parameter
Then add the viz to a dashboard. My published viz is here.
For this week’s challenge, Kyle looked to solve a problem that he’s seen discussed within another blog – how to solve a highlighting problem when filtering donut charts.
I’ve been away on a little holiday abroad for a family wedding, so am on catch up this week. So I’m going to make this as brief as I can as time is limited.
Building the donut charts
Use the steps described in this blog post I wrote for my company to build a donut chart using the dual axis method.
For the Category donut chart, you will need Category on Colour and Sales on Angle of the outer Pie Chart. For the inner circle, you will need to add Sales to Text. Adjust the text as required. Sales needs to be formatted to $ with 0 dp.
For the Sub-Category donut chart, you will need to add Category to Colour. Then add Sub-Category to Detail and click on the 3 dots to the left of the Sub-Category pill and change to also add to Colour.
To adjust the colours, edit the colour legend, select all the options within the same Category. Select a sequential colour palette that matches the core colour for the category, then select Assign Palette. The colours should change to a range of that colour.
Create a new field
# Products
COUNTD([Product ID])
and add this to the Angle shelf. Add Sales to the Tooltip shelf and adjust the tooltip.
For the inner circle, add #Products to Text. Adjust the text as required
Filtering the donut
Add the two sheets to a dashboard. Add a dashboard filter action
Filter Cat
On select of the Category donut, target the Sub-Category donut chart passing in all fields. Keep filtered values when selection cleared.
Stopping the Category donut from being highlighted
Create new fields
True
TRUE
False
FALSE
and add these to the Detail shelf on the All Marks card of the Category donut sheet.
Then create a dashboard filter action
Unhighlight
On select of the Category donut on the dashboard, target the Category donut sheet itself, passing in the fields Tue = False. Show all values when selection cleared.
Now when the Category donut is clicked on, the other segments won’t fade. However, the selection is still visible – the edges of the pie are displayed.
Stop showing the selected section of the pie
For this we employ a trick mentioned in the blog post referenced in the challenge. Create a new field
Dummy
‘Dummy’
and add this the Detail shelf of a new sheet. Change the mark type to polygon so nothing is visible.
Add this to the dashboard as a floating object – make it small and place somewhere inconspicuous
Whilst the selections will still be visible when testing on Desktop, once published to Tableau Public, the presence of the polygon forces the whole dashboard to be rendered server side rather than client side. This reduces the amount of interactivity, and consequently the pie chart segments don’t display when clicked.
It was Luke’s turn to set the #WOW2023 challenge this week and he chose to focus on remaking a visualisation relating to the change in the Antarctic Sea Ice, inspired by charts created by Zach Labe.
The challenge involved the use of extensive calculations, which at times I found hard to validate due to the steps involved in reaching the final number, and only having visibility of the final number on hover on a point on the chart. If it didn’t match, it became a bit of a puzzle to figure out where in the process I’d gone wrong.
Getting the data for the faded yearly line charts was ok, but I ended up overthinking how the decade level darker line chart was calculating and couldn’t get matches. Anyway, after sleeping on it, I realised my error, and found I didn’t need half the calculations I’d been playing with.
So let’s step through this. As we’re working with moving averages, we’re looking at using table calculations, so the starting point is to build out the data and the calculations required into a tabular form first.
Setting up the calculations
I used the data stored in the Google sheet that was linked to in the challenge, which I saved down as a csv file. After connecting to the file, I had separate fields for Day, Month and Year which I changed to be discrete fields (right click on field and Convert to discrete).
We need to create two date fields from these fields. Firstly
Actual Date
MAKEDATE([Year],[Month],[Day])
basically combines the 3 separate fields into a proper date field. I formatted this to “14 March 2001” format.
Secondly, we’ll be plotting the data on an axis to span a single year. We can’t use the Actual Date field for that as it will generate an axis that runs from the earliest date to the latest. Instead we need a date field that is ‘normalised’ across a single year
Date Normalise
MAKEDATE({max([Year])}, [Month], [Day])
the {max([Year])} notation is a short cut for {FIXED: MAX([Year])} which is a level of detail (LoD) expression which returns the greatest value of the Year field in the data set. In this case it returns 2023. So the Date Normalise field only contains date for the year 2023. Ie if the Actual Date is 01 Jan 2018 or the Actual Date is 01 Jan 2020, the equivalent Date Normalise for both records will be 01 Jan 2023.
Let’s start to put some of this out into a table.
Put Year on Columns, and Date Normalise as a blue (discrete) exact date field on Rows. Add Area(10E6M2) to Text and change to be Average rather than Sum (in leap years, the 29 Feb seems to have been mapped to 01 March, so there are multiple entries for 01 March). This gives us the Area of the Ice for each date in each year.
We need to calculate the 7 day moving average of this value. The easiest was to do this is add a Moving Average Quick Table Calculation to the pill on the Text shelf.
Once done, edit the table calculation, and set so that is average across the previous 6 entries (including itself means 7 entries in total) and it computes down the table (or explicitly set to compute by Date Normalise).
It is best to create an explicit instance of this field, so if you click on the field and press ctrl while you drag and drop it into the data pane on the left hand side, you can then rename the field. I named mine
Moving Avg: Area
WINDOW_AVG(AVG([Area (10E6M2)]), -6, 0)
It should contain the above syntax as that’s what the table calculation automatically generates. If you’re struggling, just create manually and then add this into the table instead.
Add Area (10E6M2) back into the table too. You should have the below, and you should be able to validate the moving average is behaving as expected
Now we need to work out the data related to the ‘global’ average which is the average for all years across a single date.
Average for Date
{FIXED [Date Normalise]: AVG([Area (10E6M2)])}
for each Date Normalise value. return the average area.
Pop this into the table, and you should see that you have the same value for every year across each row.
We can then create a moving average off of this value, by repeating similar steps above. In this instance you should end up with
Moving Avg Date
WINDOW_AVG(SUM([Average For Date]), -6, 0)
Add into the table, and ensure the table calculation is computing by Date Normalise and again you should be able to validate the moving average is behaving as expected
Note – you can also filter out Years 1978 & 1979 as they’re not displayed in the charts
So now we have the moving average per date, and the global moving average, we can compute the delta
Ice Extent vs Normal
[Moving Avg: Area] -[Moving Avg Date]
Format this to 3 dp and add to the table. You should be able to do some spot check validation against the solution by hovering over some of the points on the faded lines and comparing to the equivalent date for the year in the table.
This is the data that will be used to plot the faded lines. For the bolder lines, we need
Decade
IF [Year] = {max([Year])} THEN STR([Year]) ELSE STR((FLOOR([Year]/10))*10) + ‘s’ END
and we don’t need any further calculations. To verify, simply duplicate the above sheet, and then replace the Year field on Columns with the Decade field. You should have the same values in the 2023 section as on the previous sheet, and you should be able to reconcile some of the values for each decade against marks on the thicker lines.
Basically, the ‘global’ values to compare the decade averages against are based on the average across each individual year, and not some aggregation of aggregated data (this is where I was overthinking things too much).
Building the viz
On a new sheet add Date Normalise as a green continuous exact date field to Columns, and Ice Extent vs Normal to Rows. Add Year to Detail and Decade to Colour. Adjust colours to suit and reduce to 30% opacity. Reduce the size to as small as possible. Add Decade to Filter and exclude 1970s. Ensure both the table calculations referenced within the Ice Extent vs Normal field are computing by Date Normalise only.
Add Actual Date to the Tooltip and and adjust the tooltip to display the date and the Ice Extent vs Normal field in MSM.
Now add a second instance of Ice Extent vs Normal to Rows. On the 2nd marks card that is created, remove Year from Detail and Actual Date from Tooltip. Increase the opacity back up to 100% and increase the Size of the line. Sort the colour legend to be data source order descending to ensure the lines for the more recent decades sit ‘on top’ of the earlier ones.
Modify the format of the Date Normalise field to be dd mmmm (ie no year). Adjust the Tooltip as below
Make the chart dual axis and synchronise the axis. Remove the right hand axis.
Edit the axis titles, remove row and column dividers and add row & column gridlines.
Adding the labels
We want the final point for date 18 June 2023 to be labelled with the actual Area of ice on that date and the difference compared to the average of that date (not the moving average). I create multiple calculated fields for this label, using conditional logic to ensure the value only returns for the maximum date in the data
Max Date
{max([Actual Date])}
Label:Date
IF MIN([Decade])=’2023′ AND MIN([Actual Date])=MIN([Max Date]) THEN MIN([Max Date]) END
Label: Area
IF MIN([Decade])=’2023′ AND MIN([Actual Date])=MIN([Max Date]) THEN AVG([Area (10E6M2)]) END
Label:Ice Extent v Avg for Date
IF MIN([Decade])=’2023′ AND MIN([Actual Date])=MIN([Max Date]) THEN AVG([Area (10E6M2)]) – SUM([Average For Date]) END
Label:unit of measure
IF MIN([Decade])=’2023′ AND MIN([Actual Date])=MIN([Max Date]) THEN ‘MSM’ END
Label: unit of measure v avg
IF MIN([Decade])=’2023′ AND MIN([Actual Date])=MIN([Max Date]) THEN ‘MSM vs. avg’ END
All these fields were then added to the Text shelf of the 2nd marks card and arranged as below, formattign each field accordingly
And this sheet can then be added to the dashboard. The legend needs be adjusted to arrange the items in a single row.
Sean set the challenge this week to build a custom relative date filter. He uttered the words “this week is pretty straightforward” which always makes me a bit nervous…
The premise was to essentially replicate the out of the box relative date filter. I had a good play with Sean’s solution, hovering my mouse over various fields, to see if there were any clues to get me started. I deduced the filter box, was a floating container, which stored multiple other worksheets and some parameters used to drive the behaviour.
I worked out that I think I’d need to build at least 4 sheets – 1 for the main chart, 1 to manage the date part ‘tabbed’ selector at the top of the relative date control, 1 to manage the radio button selections within the relative date control, and 1 to display the date range.
So let’s crack on.
Building the initial chart
I used the Superstore data from v2022.1, so my dates went up to 31 Dec 2022.
We need to create a line chart that shows the dates at a user defined date level – we need a parameter to control this selection
pDateLevel
string parameter containing the relevant date parts in a list, and defaulted to ‘Week’. Notice the Value fields are all lower case, while the Display As as all proper case. The lower case values is important, as this parameter will be fed into date functions, which expect the string values to be lower case.
Then we need a new date field to show the dates at the level specified
Display Date
DATE(DATETRUNC([pDateLevel], [Order Date]))
Then add this field onto Columns as a continuous exact date (green pill) and add Sales to Rows. Show the pDateLevel parameter and test the chart changes as the parameter does.
Name this sheet Chart or similar. We’ll come back to it later, as we need to ensure this data gets filtered based on the selections made in the relative date control.
Setting up a scaffold data source
So there were no additional data source files provided, and also no ‘you must do it this way’ type instructions, so I decided to create my own ‘scaffold’ data source to help with building the relative date control. Doing this means the concept should be portable if I wanted to add the same control into any other dashboards.
I created an Excel file (RelativeDate_ControlSheet.xslx) which had the following sheets
DateParts
A single column headed Datepart containing the values Year, Quarter, Month, Week, Day
Relative Options
3 columns Option ID (containing values 1-6), Option Type (containing values date part, n periods, to date) and Option Text (containing values Last, This, Next, to date)
I added both of these sheets as new data sources to my workbook (so I have 3 data sources listed in my data pane in total).
Building the Date Part Selector
From the DateParts data source, add the Datepart field to both the Rows and Text fields. Manually re-sort by dragging the values to be in the required order.
Manually type in MIN(1) into the Rows shelf and edit the axis to be fixed from 0-1. Align the text centrally.
We’re going to need another parameter to capture the date part that will be clicked.
pDatePartSelected
string parameter defaulted to ‘quarter’ (note case)
We also need a new calculated field to store the value of the date parts in lower case
Datepart Lower
LOWER([Datepart])
and then we can use these fields to work out which date part has been selected on the viz
Selected Date Part
[pDatePartSelected] = [Datepart Lower]
Add this to the Colour shelf, and adjust colours accordingly (False is white).
Remove headers, column/row dividers, gridlines and axis, and don’t show tooltips.
Finally, we’re going to need to ensure that when a value is clicked on in the dashboard, it doesn’t remain ‘highlighted’ as we’re using the colouring already applied to define the selection made.
In the DateParts data source, create new calculated fields
True
TRUE
False
FALSE
and add both these fields to the Detail shelf, along with the Datepart Lower field. We’ll refer to all these later when we get to the dashboard.
Building the Relative Date Radio Button Selector
In the Relative Options data source, create the following fields
Row
IF ([Option ID] %3) = 0 THEN 3 ELSE ([Option ID] %3) END
Column
INT([Option ID]<=3)
These fields will be used to define where each entry in our scaffold data source will be positioned in the tabular display.
We then need to get all the text displayed as is expected, but firstly we need to define the parameter that will capture n number of days, which is incorporated into the label displayed
pSelectPeriods
integer parameter
We can now create
Option Label
IF [pDatePartSelected] = ‘day’ THEN IF [Option ID]=1 THEN ‘Yesterday’ ELSEIF [Option ID] =2 THEN ‘Today’ ELSEIF [Option ID] = 3 THEN ‘Tomorrow’ ELSEIF [Option Type] = ‘n periods’ THEN [Option Text] + ‘ ‘ + STR([pSelectPeriods]) + ‘ ‘ + [pDatePartSelected] +’s’ ELSE UPPER(LEFT([pDatePartSelected],1)) + MID([pDatePartSelected],2) + ‘ ‘ + [Option Text] END ELSE IF [Option Type] = ‘date part’ THEN [Option Text] + ‘ ‘ + [pDatePartSelected] ELSEIF [Option Type] = ‘n periods’ THEN [Option Text] + ‘ ‘ + STR([pSelectPeriods]) + ‘ ‘ + [pDatePartSelected] +’s’ ELSE UPPER(LEFT([pDatePartSelected],1)) + MID([pDatePartSelected],2) + ‘ ‘ + [Option Text] END END
This looks a bit convoluted and could possibly be simplified… it grew organically as I was building, and special cases had to be added for the ‘yesterday’/’today’/’tomorrow’ options. I could have added more detail to the the scaffold to help with all this, but ‘que sera’…
Add Column to Columns and Row to Rows. Add Option ID to Detail and Option Label to Text. Manually re-sort the columns so they are reversed
We’re going to need to identify which of the options has been selected. This will be captured in a parameter
pOptionSelected
integer parameter defaulted to 2
and then we need to know if the row of data matches that selected, so create a calculated field
Selected Relative Date Option
[Option ID] = [pOptionSelected]
Manually type in to columns Min(0.0), then change the mark type to shape and add Selected Relative Date Option to the Shape shelf, and amend the shapes to be an open and filled circle. Change the colour to grey.
As before, remove all headers, axis, gridlines, row/column dividers and don’t show the tooltip. Also we’ll need those True and False fields on this viz too – you’ll need to create new calculated fields in this data source, just like you did above, and then add them to the Detail shelf.
Restricting the dates displayed
Now we need to work on getting the line chart to change based on the selections being made that get captured into parameters. But first we need one more parameter
pAnchorDate
date parameter, defaulted to 31 Dec 2022
From playing with Sean’s solution, what date is actually used when filtering can vary based on the date part that is selected. Eg if you choose Month, next 2 months, anchor date of 15 Dec 2022 and choose to display data at the day level, you’ll see all the data for the whole of December rather than just up to 15 Dec (there is no Jan 23 data to display – so you actually only see 1 month’s worth of data). Based on these observations, I created another calculated field to run the filtering against.
CASE [pOptionSelected] WHEN 1 THEN ([Order Date] >= DATEADD([pDatePartSelected],-1,[Compare Date]) AND [Order Date]< [Compare Date])
WHEN 2 THEN ([Order Date]>= [Compare Date] AND [Order Date] < DATEADD([pDatePartSelected], 1, [Compare Date]))
WHEN 3 THEN ([Order Date]>= DATEADD([pDatePartSelected],1,[Compare Date]) AND [Order Date] < DATEADD([pDatePartSelected],2,[Compare Date]))
WHEN 4 THEN ([Order Date]> DATEADD([pDatePartSelected],-1*[pSelectPeriods],[Compare Date]) AND [Order Date]<= [Compare Date])
WHEN 5 THEN ([Order Date] >= [Compare Date] AND [Order Date]< DATEADD([pDatePartSelected],[pSelectPeriods],[Compare Date]))
WHEN 6 THEN ([Order Date] >= [Compare Date] AND [Order Date]<= [pAnchorDate]) ELSE FALSE END
This returns a boolean true or false. Add this field to the Filter shelf of the line chart and set to True.
If you show all the parameters on the view, you can play around manually altering the values to see how the line chart changes
Building the data range display
On a new sheet, add Dates To Show to the Filter shelf and set to True. Then add Order Date as a discrete exact date (blue pill) to Text and change the aggregation to Min. Repeat adding another instance of Order Date and change the aggregation to Max. Format the text accordingly.
Note at this point, I have reset everything back to be based on the original defaults I set in all the parameters.
Finally, there’s one additional sheet to build – something to display the ‘value’ of the option selected in the relative date filter control.
On a new sheet, using the Relative Options datasource, add Selected Relative Date Option to Filter and set to True. Then add Option Label to Text. Add row and column dividers, so it looks like a box.
Putting it all together
On a dashboard, add the Chart sheet (without title). Position the pDateLevel parameter above the chart, and add the input display box we built last, next to it (remove the title). Add a title to the whole dashboard at the top. Then remove the container that contains all the other parameters etc which don’t want to display.
You should have something similar to the below.
Change the title of the pDateLevel parameter and then adjust the input box so it looks to be in line – this can be fiddly, and there’s no guarantee that it’ll be aligned when it gets published to server. You may have to adjust again once published. You can try using padding – ultimately this is a bit of trial and error.
Now add a floating vertical container onto the sheet (click the Floating button at the bottom of the Dashboard panel on the left and drag a vertical object onto the canvas). Resize and position where you think it should be. Add a dark border to the container and set the background to white.
Now click the Tiled button at the bottom of the dashboard panel on the left, and add the Date Part Selector sheet into the container. Remove the title, and also remove any legend that automatically gets added.
Now add a line : add a blank object beneath the date part selector. Edit the outer padding of this object to be 10 px on the left and right and 0 px on the top and bottom. Change the background colour to grey, then edit the height of the object to 1px.
Now add the radio button chart beneath the line (fit entire view)., and again delete any additional legend, parameters added
Now add a Horizontal container beneath the radio button chart. Then click on the context menu of the radio button chart and select parameter > pAnchorDate to display the anchor date on the sheet. Hopefully this should land in the horizontal container, but if it doesn’t, you’ll have to move it. Add the pSelectPeriods parameter this way too.
Change the titles of these parameters as required, then add another line blank object using the technique described above, although delay changing the height for now.
Add the Date range sheet below, and fit to entire view, and now you can edit the height of the line blank object to size 1px.
Next select the container object (it will have a blue border when selected like below) and use the context menu to select Add Show/Hide Button
This will generate a little X show/hide floating object. Use the context menu of this object to Edit Button
I used powerpoint to create the up/down triangle images, which I then saved and referenced via the ‘choose an image’ option
I then added a border to this option and positioned it (through trial and error) where I wanted it to be.
Adding the interactivity
The final step is to now add all the required dashboard action controls.
At least, this is the final step based on how I’ve authored this post. In reality, when I built this originally, I added the objects to the dashboard quite early to test the functionality in the various calculated fields I’d written, so it’s possible you may have already done some of this already 🙂
We need to set which date part has been selected on the control. This is a parameter action
Set Date Part
On select of the Date Part Selector sheet, set the pDatePartSelected parameter, passing through the value of the associated Datepart Lower field.
Add another parameter to capture which radio button has been selected
Select Relative Date Option
On select of the Relative Date Selector sheet, set the pOptionSelected parameter, passing through the value of the associated Option ID field (ensuring aggregation is set to None).
Then we need to add dashboard filter actions to stop the selected options from being highlighted when clicked. Create a filter action
Deselect Dale Part Selecteor
on select of the Date Part Selector view on the dashboard, target the Date Part Selector sheet directly, passing the fields True = False, and show all values when the selection is cleared
Apply a second filter action using the same principals but with the Relative Date radio button selector sheet instead.
And with all that, the dashboard should now be built and functioning as required.
Luke Stanke returned for this week’s challenge, to build a pareto chart & bar chart on an unsynchronised dual axis. The crux of this challenge is table calculations, so as with any challenge like this, I’m going to build out what I need in tabular form first, so I can thoroughly validate I’m getting the right values. Once that is done, I’ll build the chart, then finally I’ll look at how to get the measures needed for the subtitle text.
Defining the core calculations
Building the chart
Working out the measures for the subtitle
Defining the core calculations
For the pareto, we need to plot % of orders against cumulative profit, so we need to build up some fields to get to these.
Add Order ID to Rows and Profit to Text and sort by Profit descending.
For the cumulative profit, we can add a Running Total Quick Table Calculation to the Profit pill
Add another Profit pill back into the view, and you can see how the table calculation is adding up the values of the Profit from the previous rows.
The triangle symbol indicates the field is a table calculation. By default, if you edit the table calculation, the calculation is computing down the table. I always choose to ‘fix’ how my calculations are computing, so that the values don’t inadvertently change if I move the pill elsewhere. So I recommend you set the table calc to Compute Using Order ID
I also want to ‘bake’ this table calculation into the data model (ie create a dedicated calculated field) that I can pick up and reuse. The simplest way to do this is to press Ctrl, then drag the field into the left hand data field pane (this will effectively copy the field rather than remove it from the view). Name the field and then you can verify it’s contents.
Cumulative Profit
RUNNING_SUM(SUM([Profit]))
So that’s one of the measures we need. Onto the next.
First of all we need to get a cumulative count of the number of orders.
Count Orders
COUNTD([Order ID])
Add this to the measures and it will display the value 1 per row (since each row is an order). Add a Running Total Quick Table Calculation to this field too, and again set to Compute UsingOrder ID. ‘Bake’ this into the data model too, by dragging the field as described above, and create a new field
Cumulative Order Count
RUNNING_SUM([Count Orders])
Now we need to get a handle on the total number of orders. I could do this with a LoD, but will stick with table calcs
Total Order Count
WINDOW_SUM([Count Orders])
Add to the view, and compute using Order ID again.
Now we can calculate the cumulative % of total orders
Cumulative % of Total Orders
[Cumulative Order Count]/[Total Order Count]
Format this to a % with 2 dp.
Add to the view and again compute using Order ID. You should see the values increase until 100%.
NOTE – I could have got this value by adding a Running Total table calculation to the Order Count field, and then editing that table calculation and adding a secondary table calculation to get to the % of total. However, I want to be able to reference the output of this field later on, so having a dedicated calculated field is the better option.
Ok, so now we have the 2 measures we need to plot the basic chart – Cumulative Profit and Cumulative % of Total Orders.
Building the chart
I typically start by duplicating the data sheet and then moving pills around
Duplicate Sheet
Remove Cumulative Order Count and Total Order Count
Move Order ID to the Detail shelf. Reset the sort on this pill to sort by Profit Descending
Remove Measure Names
Move Cumulative Profit to Rows
Move Cumulative % of Total Orders to Columns
Move Profit to Tooltip
Change mark type to Line
Add Sales to Tooltip and adjust tooltip accordingly
The chart needs to be coloured based on whether the marks has a profit > 0 or not. So for this we need
Profit is +ve
SUM([Profit]) >0
Add this to the Colour shelf and adjust accordingly.
Now we can add the second axis by adding Sales to the Rows shelf, then
Change mark type of the Sales marks card to bar
Remove the Profit is +ve field from the Colour shelf
Change the size to the smallest value
Adjust the tooltip
Make dual axis
Bring the Cumulative Profit axis to the front (right click on the axis > move marks to front)
Now the chart just needs to be formatted
remove column and row borders
edit the axis titles
format all the axes to to 8pt, and change the font of the axis title to Times New Roman
format the % of Total Orders axis to be 0dp
Working out the measures for the subtitle
For this, we are going to revert back to the tabular view.
We need to identify the point at which the Profit value starts to become negative. Let’s add the Profit is +ve field to Rows.
We’re looking for the row highlighted, which is the row where the previous value is true, while itself is false, which is achieved by
Profitable Marker
LOOKUP([Profit is +ve],-1) AND NOT([Profit is +ve])
Let’s add this now (ensuring the compute using Order ID)
We need to get a handle on the Cumulative % of Total Orders value for this row, but spread it across all the rows in data set, which we can do by
% of Total Profitable
WINDOW_MAX(IF [Profitable Marker] THEN [Cumulative % of Total Orders] END)
Add this on, compute by Order ID, and you can see the value for the ‘true’ line is displayed against every row. Format this field to % 0 dp.
For the potential profitability decrease, we need to get the Cumulative Profit value for the Profitable Marker row, along with the final (total) Cumulative Profit value.
Total Cumulative Profit
WINDOW_MAX(IF LAST()=0 THEN [Cumulative Profit] END)
This takes the value from the very last row in the data and again spreads across the all the rows.
With this, we can now work out the potential decrease
Potential Profitability Decrease
WINDOW_MAX(IF [Profitable Marker] THEN ([Cumulative Profit]-[Total Cumulative Profit])/[Cumulative Profit] END)
of the profitable marker row, take the difference between the ‘current’ cumulative profit and the final cumulative profit, as a proportion of the current value. Spread this across every row. Format to % of 0dp.
Now, as we have worked out these 2 values, % of Total Profitable and Potential Profitability Decrease to be the same across every row, you can add them to the Detail shelf of the All marks card on the chart viz, and reference them in the Title of the viz. (Don’t forget to ensure all table calc fields are set to compute using Order ID).
Donna + DataViz 