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.

Happy vizzin’!

Donna