Tag: RUNNING_SUM()

Elf Economics

Donna ColesLeave a comment

For the penultimate challenge of 2023, Erica set this fun Christmas themed challenge to visualise the toy production in Santa’s workshop. It was a collaboration with the #PreppinData crew, where you were encouraged to complete their challenge to prep the data for this one. I did do that, but to ensure no discrepancies or field name differences, I used the outputs from the challenge itself as the source for my viz.

Building the Line Chart

This needs to show the quota vs the cumulative number of toys produced for each production manager/toy and uses the data from the Output 1 of the Prep challenge.

Add Week to Columns and change to exact date. Format the Week pill on the Columns to show as custom format yyyy on the axis

then edit the axis and set the tick marks to be fixed from 01 Jan 2023 with an interval of 1 year. This will result in just 2 axis labels displayed, one for 2023 and one for 2024

Add Production Manager and Toy to Rows and then add Quota to Rows too. Then drag Toys Produced onto the Quota axis and drop it when the double green column icon appears.

This will convert the viz to have Measure Values on the Rows instead, and the Quota and Toys Produced pills sitting in the Measure Values section on the left.

Add a Running Total quick table calculation against the Toys Produced pill. Then edit the Value axis, so that the axis are independent axis ranges for each row & column.

The colour of the running total line needs to change based on whether the overall value is above or below the quota. Erica asked us not to use LODs in this challenge, so to determine this, we need

Colour – Over | Under

IF WINDOW_MAX(RUNNING_SUM(SUM([Toys Produced]))) > WINDOW_MAX(SUM([Quota])) THEN ‘Over’ ELSE ‘Under’ END

The WINDOW_MAX function is taking the highest value of the measure and essentially ‘spreads’ that across every row of data being plotted (in this case every week).

Add this field to the Detail shelf and then click on the 3 dot symbol to the left of the pill and change it to the Colour symbol. This allows multiple pills to be on the Colour shelf – Measure Names and Colour – Over | Under, resulting in 4 different colours in the colour legend.

Adjust the legend colours, so the two relating to the Quota are the same colour and the others coloured based on whether the value is Over or Under.

On the Label shelf, check the show mark labels option, and then select most recent. Adjust the font to be bold and match mark colour. Format both the pills sitting in the Measure Values section to be Millions with 1 dp.

Add Week to the Tooltip shelf and format to be in the <day of week>, <day> <month> <year> style. Adjust the tooltip accordingly.

Hide the Production Manager and Toy fields (uncheck show header). Edit the title of the Value axis and the Week axis. Remove all gridlines, zero lines, row & column dividers, but ensure the axis are displayed. Change the worksheet background colour.

Update the Title of the sheet to reference the Toy, then name the sheet Line or similar.

Building the KPIs

We’re still using the data from Output 1. We’re going to do this in 2 sheets, as we want to format the text of the PM name differently. To start, we need some additional calculated fields.

Rate of Production

AVG([Toys Produced])

Then we need to work out for those Production Managers who were under their quota, how far off they were and how long, based on their production rate, it would take for them to fulfil that difference. So first we need

Difference

AVG([Quota]) – SUM([Toys Produced])

This gives us how far under (or over) the PM was from their target quota.

We can then calculate

Weeks Needed to Meet Quota

IF MIN([Over or Under Quota?]) = ‘Over’ THEN 0 ELSE
CEILING([Difference] /[Rate of Production])
END

If the PM has exceeded their quota, then 0, as there’s nothing to build, otherwise determine the number of whole weeks. The CEILING function ensures even if the result is only a fraction over a number, the result is ’rounded up’ the next whole number so 12.1 weeks and 12.9 weeks are both reported as 13 weeks.

Add Production Manager and the 2 fields above onto a new sheet and display in tabular form.

Set the sheet to Entire View and adjust the text to be larger (I used bold 18pt font). Format the column headings to be larger too (I used 12pt). Stop the tooltips from displaying, remove row/column dividers and row banding. Set the background colour of the worksheet and hide the Production Manager column (uncheck show header).

Name this sheet KPI or similar.

On a new sheet, add Production Manager to Rows and add Production Manager to the Text shelf too. Double click in to the Columns shelf and type ‘Production Manager’ to create a heading for the text column.

Set the sheet to Entire View, then adjust the font of the Text shelf. I chose a handwriting script font and set to 18pt and bold. The hide the Production Manager field on Rows, and hide the ‘Production Manager’ column label heading (right click – hide field labels for columns). Adjust the font of the column heading and remove all row/column dividers and row banding, Set the background colour. Hide the tooltip.

Name the sheet PM Name or similar.

Building the bar chart

For this, we’re now using the data from Output 2.

We’re plotting 2 measures for the bars – the amount under or over the quota which is a +ve (over) or -ve (under) number which will be plotted either side of a zero line as you would expect. The Toys Over/Under Quota field has this value.

We also need to plot the amount of toys produced, but while this is a positive number, it is displayed on the bar chart on the negative side of the zero line. So to enable this we need

Toys Produced to Plot

-1 * [Toys Produced]

ON a new sheet, add List and Toy to Rows. Then add Toys Produced to Plot to Columns, and then drag Toys Over/Under Quota onto the axis and drop when the 2 green column icon appears. This will result in the following display where Measure Names and Measure Values are automatically added.

Move Measure Names from Rows onto Colour, then change the order of the pills listed in the Measure Values section, so Toys Produced to Plot is listed first.

Create a new field

Colour – Over | Under

IF [Toys Over/Under Quota] < 0 THEN ‘under’ ELSE ‘over’ END

and add to the Detail shelf, then adjust the symbol to add this field to the Colour shelf as well to give you 4 colours on the legend. Adjust accordingly. Add Quota, Toys Produced and Toys Over/Under Quota to Tooltip and adjust.

For the label to display against each bar, we need to plot another measure, which is either 0 for those which were under production, or the value of the over production.

Label Value to Plot

IF SUM([Toys Over/Under Quota]) < 0 THEN 0 ELSE SUM([Toys Over/Under Quota]) END

Add this to Columns. On the Label Value to Plot marks card, change the mark type to circle and remove Measure Names from colour.

Create a new field

% Difference

SUM([Toys Over/Under Quota]) / SUM([Quota])

and apply a custom number format of 0%;0% which means -ve numbers will display as +ve.

Add this to the Label shelf along with Colour – Over | Under. Adjust the label text so the labels are displayed on a single line, are aligned middle right and the font matches mark colour and is bold. Make the chart dual axis and synchronise the axis (set the mark type of Measure Values to a bar if the display changes). On the Label Value to Plot marks card, reduce the opacity of the circle colour to 0% and reduce the size to the smallest possible. Remove all the text from the Tooltip.

To ensure the label text doesn’t overlap the bars, we can extend the axis by creating

Ref Line

WINDOW_MAX([Label Value to Plot]) *2

Add this to the Detail shelf of the Label Value to Plot marks card. Then right click on the top axis and Add Reference Line that refers to the maximum of the Ref Line field. Apply settings as below so the line is invisible.

Finally hide both axis, remove all gridlines, zero lines, axis and column dividers. Adjust the row dividers to be thick grey dashed lines. Update the title of the sheet.

Name the sheet Bar or similar.

Adding the interactivity

Using layout containers, add the sheets to a dashboard so they are arranged in the format required. Then add a dashboard filter action

Select PM

on select of the Bar sheet, target the KPI, Line and PM Name sheets. When the selection is cleared, keep filtered values Only allow 1 selection to be made at a time.

Click the bar against Barbie Doll to set the other charts to filter just to that toy, then unclick the bar again. The remaining charts should stay filtered.

And that should be it. Obviously you can add imagery as you wish but I didn’t go down that route – I just chose to set coloured borders on the layout containers.

My published viz is here.

Happy vizzin’! and enjoy the festive season.

Donna

What is the lifetime value of customers?

Donna ColesLeave a comment

Luke Stanke set the #WOW2022 challenge this week (see here) asking us to visualise the lifetime value of customers. I have to admit, I did struggle to really understand what was being requested, and to get the numbers to match Luke’s. I ended up referring to my own blog post on a similar challenge Ann Jackson set in week 2 of 2021 to get the calculations I needed 🙂

We first need to define the quarter that each customer made their first purchase.

Customer First Order Quarter

DATE(DATETRUNC(‘quarter’, {FIXED [Customer ID]:MIN([Order Date])}))

The {FIXED LOD} calculation returns the minimum order date per customer, then truncates this to the first day of the quarter that date falls in.

We then need to determine the difference in quarters between the Customer First Order Quarter and the quarter associated to the Order Date of each order in the data set. The requirement indicated we needed to add 1 to the result. I split this into multiple calculated fields. Firstly,

Order Date Quarter

DATE(DATETRUNC(‘quarter’, [Order Date]))

gets me the quarter of each Order Date, then

Quarters Since First Purchase

DATEDIFF(‘quarter’, [Customer First Order Quarter], [Order Date Quarter])+1

Let’s pop these fields out into a table to check things are behaving as expected.

Add Customer First Order Quarter and Order Date Quarter to Rows as discrete exact dates (blue pills), and add Quarters Since First Purchase to the Text field, but set it to be a dimension, so it is disaggregated.

Now we need to count the number of distinct customers that made a purchase in each Customer First Order Quarter (the cohort)

Count Customers Per Cohort

{FIXED [Customer First Order Quarter]: COUNTD([Customer ID])}

Add this to to the sheet, along with Sales (I moved Quarters Since First Purchase to rows too)

As expected, we can see the same customer count for each Customer First Order Quarter cohort.

We’ve now got all the building blocks to move on the the next requirement, but I’m going to rearrange the table a bit, to start to reflect the data actually needed for the output.

The x-axis of the chart is going to be based on the Quarters Since First Purchase field, so move that to be the first column of data (1st entry in Rows). Then remove Customer First Order Quarter and Order Date Quarter, as we don’t need this level of information in the final viz.

We now have the sum of all the customers and the total sales, so we can now create the division reqiurement

Sales / Customer

SUM([Sales])/SUM([Count Customers Per Cohort])

I set this to currency $ with 0 dp.

Add this to the table

Then to make this a running total, create a Running Total quick table calculation off of this field (right click on field -> Quick Table Calculation -> Running Total). Add back in Sales/ Customer.

We’ve now got all the components needed to build the viz, but do require an additional calculation to be displayed in the tooltip, which is the difference between each row.

Right click the existing running total Sales / Customer pill (the one with the triangle) and choose to Edit Table Calculation. Tick the Add secondary calculation checkbox and choose the Difference From table calc to run down the table, making the calc relative to the previous row.

Re-add a Running Total quick table calc to the other Sales / Customer pill, and then add Sales / Customer back into the view (it’s annoying that Tableau won’t let you add multiple pills of the same measure name unless they have a different calculation against them).

The snag with this is that we need a value to display for the first row. We need to create a new field that can reference the data in the second table calculation. Click and drag the ‘difference’ table calculation field into the Data pane, and name the field Difference. It should look like below, but you shouldn’t have needed to type any of that.

Difference

ZN(RUNNING_SUM([Sales / Customers])) – LOOKUP(ZN(RUNNING_SUM([Sales / Customers])), -1)

Now create a new calculated field

Tooltip:Difference

IF FIRST()=0 THEN [Sales / Customers] ELSE [Difference] END

Set this to a customer number format of 1dp, prefixed by + (the data is always cumulative so is never going to have a -ve value, so this works).

Now we can build the viz.

On a new sheet add Quarters Since First Purchase to Columns as a continuous dimension (green pill), then add Sales / Customers to Rows and set to be a Running Total quick table calc. Change the mark type to be a Gantt Bar.

Create a new field

Size

[Tooltip:Difference]*-1

and add this to the Size shelf.

Add a second instance of the Sales / Customer running total calc (press ctrl and click and drag the existing pill in rows to create another next to it). Change the mark type of this to be bar. Remove the Size pill, and then click the Size shelf button, and set the Size to be fixed, aligned left.

Now make the charts dual axis and synchronise the axis. Set the colour of each mark type to the relevant palette, and set the mark borders to None. Hide the right hand axis.

On the All marks card, add the Tooltip:Difference and Count Customers Per Cohort fields to the Tooltip shelf, and amend the tooltip to match.

On the bar marks card, click the Label shelf button and tick the show mark labels checkbox. Align these left.

Remove the left hand axis, remove all gridlines and row/column dividers. Add column axis ruler and dark tick marks

Edit the x-axis and rename the title to Quarter of Order.

If you find you have the x-axis going from 0 to 18, then change the datatype of Quarters Since First Purchase from a whole number to decimal. (right click pill in data pane -> change data type -> Number(decimal).

Add the chart to a dashboard and boom! you’re done. My public viz is here. (note, I did notice some vertical dividers displaying in the area chart on public, but think this is a Tableau Public ‘bug’ as I’ve switched off all the lines I can think of, and Desktop displays fine….

Happy vizzin’!

Donna

Sales Budget “Burndown” Chart

Donna ColesLeave a comment

Erica Hughes had a table-calc-tastic challenge for us this week! I was using Tableau before LoDs were invented, so became very familiar with the ‘dark art’ of table calculations, and subconsciously often turn to these first when solving problems. Since the advent of LoDs, this functionality can get forgotten about, so this is a great challenge to help flex those table calc muscles and become a good reference point.

With any table calc challenge, I work out what I need in a tabular form before even attempting any visual, so that’s where we’ll start today.

I also tend to ‘build up’ the calculated fields I need, as this helps me validate the data I’m working with. It means I end up with more calculated fields than absolutely necessary, but it’s a good practice to get in as it eases troubleshooting in the long run.

In this example, we need to treat Sales as if it’s a budget that is then being ‘spent’ over the course of the following months. The first thing we need to define is the total budget

Total Sales

TOTAL(SUM([Sales]))

We then need to understand the cumulative (running sum) of Sales per month.

Running Sum Sales

RUNNING_SUM(SUM([Sales]))

Note – this calculation can be achieved by applying a Quick Table Calculation to the Sales pill once added to a view, but I will need to reference the values in other calculated fields, so created the field directly.

Let’s start building out our table of data to see what’s going on with these calculations.

Add Order Date to Rows and set so its displaying the date in the Month Year format

Then add Total Sales, Sales and Running Sum Sales

The Total Sales column will be the same for every row and match the same value in the last row of the Running Sum Sales column. The value in each Running Sum Sales row is the sum of the Sales value in the same and all preceding rows.

By default the table calculations are working ‘down’ the table which gives the desired result, but I tend to ‘fix’ the field the calculation is computing over, as when we build the viz we won’t be going ‘down’, but ‘across’, so fixing helps ensure we get all our settings just right.

So edit the table calculation of both the Total Sales and the Running Sum Sales fields to compute using Month of Order Date.

With these fields, we can calculate the ‘sales budget’ value being displayed as

Total Less Running Sum Sales

[Total Sales]- [Running Sum Sales]

Pop this on the view and verify the table calculation settings are set as above (this field contains nested calcs, so check each setting). You should be able to verify the value of this column is the result of the 1st column – 3rd column values

But there’s a twist.

The requirements state that “for dates in the future, the sales budget should remain constant”.

For this we need to work out what month ‘today’ is in. For this task, I have hard coded ‘today’ into a parameter called Today and set to 8th March 2022. If this was a ‘real’ production business dashboard, I’d just refer to the function TODAY() directly.

I can then work out

Current Month

DATETRUNC(‘month’, [Today])

which will return 01 March 2022 in this instance.

I then want to identify the record that matches the current month

Is Current Month?

DATETRUNC(‘month’, [Order Date]) = DATE([Current Month])

which just returns a boolean True/False.

And with this, I can then determine the value from the Total Less Running Sum Sales column that is associated to March 2022, and ‘spread’ that value across every row in the view.

Curr Month: Total Less Running Sum Sales

WINDOW_MAX(IF ATTR([Is Current Month]) THEN [Total Less Running Sum Sales] END)

If the month in the row is the current month, get the required value and ‘spread’ over every other row using WINDOW_MAX. Add this to the view, checking your table calc settings again.

Now we can work out the values needed for the Sales Budget line

Sales Budget

IF MIN(DATETRUNC(‘month’, [Order Date])) > MIN(DATE([Current Month])) THEN [Curr Month: Total Sales Less Running Sum Sales] ELSE [Total Less Running Sum Sales] END

Format this to $, Millions (M), 1dp.

If the month is later than the current month, use the value associated to the current month, otherwise use the Total Less Running Sum Sales value. Note here, the date functions are wrapped within a MIN function as the other fields are table calculations which means they’ve been aggregated, so all other fields referenced need to be aggregated to. The function MAX will have worked just as well.

Phew! we’ve finally got the data we need for the first line :-). As mentioned earlier, this can be achieved by combining some of the logic in the calcs, but I like to be methodical and verify my numbers give me what I expect at each stage.

In order to display the Estimated Budget line, we need to first work out how much of the total sales would be spent each month, if the same amount was spent each month – ie Total Sales divided by number of months.

Size (Count of Months)

SIZE()

I just chose to use the SIZE() table calculation to essentially count the number of rows in my view.

Estimated Budget Constant

//average the sales over the total months to get a constant budget
[Total Sales] / [Size (Count of Months)]

Add these into the view and adjust the table calc settings as before

essentially Total Sales (2,297,201) / Count of Months (48) = Estimated Budget Constant (47,858).

Like before, we need to compute running sum of this estimated budget

Running Sum Est Budget

RUNNING_SUM([Estimated Budget Constant])

and then we can calculate the Estimated Budget

Total Less Running Sum Est Budget

[Total Sales] – [Running Sum Est Budget]

This now gives us the data to plot the 2nd line.

The final calculation we need for the tooltip is the difference between the sales budget and estimated budget

Difference to Estimated

[Sales Budget]- [Total Less Estimated Running Sum]

format this using a custom format of +”$”#,##0,K;-“$”#,##0,K

Now we can build the viz! I tend to keep sheets like above in any workbook as a ‘check sheet’ if I need to do any troubleshooting later on.

So on a new sheet, add Order Date to Rows and set to be a continuous (green) month/year format this time. Add Sales Budget to Columns. Adjust the table calculation so all nested calculations are computing by Order Date.

Add Total Less Estimated Running Sum to Columns (set the table calc settings), then change to dual axis and synchronise axis.

Remove Measure Names from the All Marks card to remove the colours that have been set. Change the Colour of the Sales Budget line to purple, and set the markers to have circles.

From the Analytics tab, drag a Trend Line to the canvas and drop it as a linear trend on the Total Less Estimated Running Sum measure

This will add the ‘dotted’ line.

On the Total Less Estimated Running Sum marks card, set the Opacity of the Colour to 0%, so only the grey dotted trend line is visible.

Edit the trendline and uncheck Show recalculated line for highlighted or selected data points

Add Current Month to the Detail shelf of the All Marks card, and set to the month/year format. Then right click on the Order Date axis and Add Reference Line, setting the values as below

Right click on the reference line, and Format; adjust the alignment and font size & colour, so ‘Future’ is listed at the top.

Add Difference to Estimated onto the Tooltip of the Sales Budget card (adjust those table calc settings). The format the tooltip accordingly.

Add Curr Month: Total Sales Less Running Sum Sales to the Detail shelf of the All Marks Card (adjust those table calc settings). Edit the title of the viz

Finally tidy up the display by removing axis, row and column banding etc, and adjust the Sales Budget axis so it displays every 500,000.

And that should be it… my published viz is here.

Happy vizzin’!

Donna

Thanksgiving Day NFL Games

Donna ColesLeave a comment

Sean Miller posted this week’s challenge based on the results of the annual NFL games hosted on Thanksgiving Day. It immediately reminded me of a previous #WOW challenge that Lorna posted in 2019 when she visualised Rugby League wins (see my viz here).

This is a table calculations based challenge. I did start using FIXED LoDs to help calculate the summary measures (Total Games and Win %) displayed at the front, but found that as there are 2 years (1975 and 1977) when the Dallas Cowboys did not host a game, I ended up with some pesky NULL values displaying which affected how the running sum area chart displayed.

Defining the calculations

As its a table calc challenge, I’ll build out what I can into a table to start with, to sense check I’m getting the correct numbers.

First up add Home Team, Game Date and Visiting Team to Rows and display Home Score and Visiting Score.

We start by determining the result of the fixture, based on whether it’s a home or away win or a tie. In the lollipop chart home wins are plotted at 1 and away wins at -1, so we’re going to store the result as a numeric value rather than text.

Result

FLOAT(IF [Home Score]>[Visiting Score] THEN 1
ELSEIF [Home Score]<[Visiting Score] THEN -1
ELSE 0 END)

The output is wrapped within a FLOAT, as this will help how the axis displays. Without it, by default Tableau will define the field to be a whole number, and the axis will extend to +/-2 which is too much room. We can’t adjust (fix) the axis to a decimal if the field itself is an integer, and adjusting to +/-1 chops off the displayed marks.

If you add this to the display, it will show 1, 0 -1 as you expect. You’ll notice though that the Axis on the lollipop chart is labelled as Win/Loss. This is achieved by applying a custom format to the field – “Win”;”Loss”;”Tie”

This is a sneaky but effective trick. The information stated before the first semi-colon applies to positive numbers, the info after the first semi-colon applied to negative numbers, and the information after the optional second semi-colon applies to zero.

Unfortunately though, it would appear that, at the point of writing, Tableau Public, isn’t honoring the zero formatting, and is displaying Win rather than Tie. The display works on Desktop though.

The win/loss/tie text is just a formatting feature and affects what is displayed, but the underlying value is still a number.

The Result field will be used to plot the lollipop chart. We now want a field to plot the area chart against. This is a running total of the Result values (ie win =1, win, win = 1+1, win, win, loss = 1+1 -1) and we need a table calculation.

However, as stated above due to a couple of missing years, I had to make an adjustment to ensure the running total displayed as Sean had in his challenge. I created another field

Result Adjusted

IIFNULL(SUM([Result]),0)

If the Result field doesn’t exist, as there is no data, then use 0 instead.

To see what’s going on, we’re going to need a different view of the data where the date field is continuous (green) rather than discrete (blue).

Build the below, and filter just for the first 10 years – you’ll see the gaps where the are no marks in 1975 and 1977 for Dallas

Use the context menu of the green YEAR(Game Date) pill and select the option to Show Missing Values. Marks will now display

Add Result to Label. Each mark is labelled Win or Loss, except the ones for Dallas for 1975 & 1977 as there is no data

Now add Result Adjusted to Label. A 0 value is now displayed against those two marks.

We can now build a running total off of this measure instead

Running Total Wins

RUNNING_SUM(([Result Adjusted]))

Add this to the Label too and verify the table calculation is computing by the Game Date field only. The running total for the 2 ‘missing’ dates is displaying a value which is the same as the previous value (since we’ve added 0 onto the running total). This will give us the flat line in the area chart when we come to build it.

Now back to our table of data, we can focus on the other calculated fields we need….

Total Games

WINDOW_COUNT(COUNTD([Game Date]))

This is a table calculation and is simply counting the number of distinct dates displayed. Add this to the table display we were building to start with, and adjust the table calculation to compute by all fields except Home Team. The total should display the same value for all the rows against each Home Team.

Next we want a field to indicate if the row is a win.

Is Win?

INT([Home Score]>[Visiting Score])

This is taking a boolean of true or false and converting to an INT (1 or 0).

From this we can work out the Win rate

Win %

WINDOW_SUM(SUM([Is Win?]))/[Total Games]

Add up all the Is Win? values associated to the Home Team as a proportion of the Total Games played. Format this field to a percentage with 0 dp. Again, add to the table and adjust the table calc to compute by all fields except Home Team, and verify the same settings applied to both the calculations nested in this calculation

For the All-Time Record, we need to know the number of wins and number of losses. We have a field to help us with the wins, but need an equivalent for the losses

Is Loss?

INT([Home Score]<[Visiting Score])

And from this we can work out

All-Time Record

STR({FIXED [Home Team]: SUM([Is Win?])}) + ‘-‘ +
STR({FIXED [Home Team]: SUM([Is Loss?])})

This is the one field I kept from my LoD based attempt.

The circles on the lollipop chart are coloured based on the difference in the score, so lets’s create that

Score Difference

[Home Score]-[Visiting Score]

And finally we need some fields to help display the tooltips properly. The tooltip indicates whether the result was ‘won’ or ‘lost’ which is different text to the axis labels.

TOOLTIP-Result

IF [Result]=1 THEN ‘won’
ELSEIF [Result]=-1 THEN ‘lost’
ELSE ‘tied’
END

The tooltip also displays the scores, but the scores are always presented as highest score – lowest score and not home score – visiting score. So we need fields to store the right values

TOOLTIP–Higher Score

IF [Is Win?]=1 THEN [Home Score] ELSE [Visiting Score] END

TOOLTIP – Lower Score

IF [Is Loss?]=1 THEN [Home Score] ELSE [Visiting Score] END

Pop all these fields out onto the table, so you can validate you’ve got all your calcs right before building the viz.

Building the area chart

Add Home Team to Rows, Game Date (continuous, show missing values) to Columns and Running Total Wins to Rows (ensure table calculation set as required). Change to mark type of Area. You should have 2 horizontal lines from 1974-1975 and 1976-1977 against the Dallas Cowboys row.

Adjust the tooltip, edit the label of the Running Total Wins axis , and remove the label of the Game Date axis.

Building the lollipop chart

Now add Result to Rows directly after the Home Team pill. Change the mark type to circle.

Add Score Difference to the Colour shelf of the circle mark, and adjust the starting colour range to a dark grey. Readjust the colour of the area chart to blue too. Add a border to the area chart too (via the colour shelf).

Add another instance of Result to the Rows shelf, next to the existing one. Set the mark type of this to bar. Reduce the size to the smallest possible, set the colour to grey and remove the border.

Now set this to be dual axis, synchronise the axis, and set the marks of the 2nd Result axis displayed on the right hand side to move marks to back. Uncheck Show Header to remove this axis from displaying.

Add Visiting Team, TOOLTIP-Result, TOOLTIP-Higher Score and TOOLTIP-Lower Score to the Tooltip shelf of both the Result marks cards, and adjust the tooltip on both to

Remove the Column dividers.

Now drag Total Games to Rows and drop next to the Home Team field. Change to be discrete (blue). Verify the number is what you expect and adjust the table calc if need be.

Add All-Time Record and Win % (set to discrete) to the view too. Then format these 4 fields so the text is larger and aligned centrally.

All that’s left now is to add the sheet to a dashboard. My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Profitability with Dual Axis Charts

Donna ColesLeave a comment

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 Using Order 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).

My published viz is here.

Happy vizzin’! Stay Safe!

Donna