Lorna set a table calculation filled challenge this week to recreate some KPI cards using an aggregated and amended version of the Superstore data set.

Lorna purposefully used a string Period field to define the timeframe to encourage the use of table calculations. No doubt, there are ways you could use string functions/regex to extract the relevant year and month number to come up with a different solution, but I’m going to head down the intended route.

And as with any table calculation based challenge, I find it best to always build out all the calculations I need into a tabular display to start with before building the viz. So let’s get started…

Building out the Calculations

We need to have a handle on more of the data than just that associated to the Period we’re interested in, so we can’t use a simple Quick Filter on the Period field to restrict the data, otherwise we can’t ‘access’ data for the previous period. So to manage which Period we want to focus on, I created a parameter

pSelectedPeriod

string field that uses a list where the values are added from the Period field. Default to P12 Y2022/23

On a new sheet, add Period to Rows and then add the Sales , Profit and Quantity measures to Text. Show the pSelectedPeriod parameter

What we’re going to do is get the values for each measure that is associated to the pSelectedPeriod, and display that value over every row.

Curr Sales

WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Sales] END))

If the Period matches that in the parameter, then get the Sales value and then use Window_MAX to ‘spread’ that value over every row

Add this to the table, and edit the table calculation to ensure the value is being computed by Period.

Repeat the same for Profit and Quantity

Curr Profit

WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Profit] END))

Curr Qty

WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Quantity] END))

If you change the parameter, you should see all the values in the last 3 columns changing to reflect the value from the first 3 columns of the relevant row.

Now we need to get the value from the previous period, ie the data from the previous row

Prev Sales

WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Sales]),-1) END)

The LOOKUP function is taking the Sales value from the previous 1 row (-1), and then WINDOW_MAX is once again ‘spreading’ this value across every row. We also need

Prev Profit

WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Profit]),-1) END)

Prev Qty

WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Quantity]),-1) END)

Add these to the table, and again adjust the table calc settings for every field to compute by Period.

Now we have the current and previous values for each measure, we can work out the % difference

Diff Sales %

([Curr Sales]-[Prev Sales])/[Prev Sales]

Format this to a % with 1 dp

Diff Profit %

([Curr Profit]-[Prev Profit])/[Prev Profit]

Diff Qty %

([Curr Qty]-[Prev Qty])/[Prev Qty]

Add all these onto the sheet, and remember the table calc settings (for these, there are nested table calcs, so make sure both are set properly).

We now need an arrow indicator to display up or down depending on the % value, and this needs to display as a different colour, so we need two fields per measure.

Diff Sales Indicator +ve

IF [Diff Sales %] >= 0 THEN ‘▲’ END

Diff Sales Indicator -ve

IF [Diff Sales %] < 0 THEN ‘▼’ END

I use this site to copy the text symbols from

Diff Profit Indicator +ve

IF [Diff Profit %] >= 0 THEN ‘▲’ END

Diff Profit Indicator -ve

IF [Diff Profit %] < 0 THEN ‘▼’ END

Diff Qty Indicator +ve

IF [Diff Qty %] >= 0 THEN ‘▲’ END

Diff Qty Indicator -ve

IF [Diff Qty %] < 0 THEN ‘▼’ END

Add these to the sheet if you wish too (apply the table calc settings), but you’ll only get a value for one or the other field depending on whether the difference was +ve or -ve. Below I’ve just added the two Sales indicators

The Tooltip displays the value of the two Periods being compared. One of these is in the parameter, but we need to capture the other

Previous Period

WINDOW_MAX(IF [pSelectedPeriod] = MIN([Period]) THEN LOOKUP(MIN([Period]),-1) END)

So now we have all the values we need for the KPIs captured against every row in the dataset. So now we want to just show a single row. It could be the first, it could be the last… based on Lorna’s hint, let’s filter to just show the row related to the pSelectedPeriod value.

Filter Selected Period

[pSelectedPeriod] = LOOKUP(MIN([Period]),0)

Using the offset of 0 with the LOOKUP, returns the value for the row you’re on, so adding this to the FIlter shelf and selecting True, filters the display to the row where the Period matches the parameter. NOTE if you adjust the table calc settings of this field after adding to the filter shelf, you’ll need to reselect the option to filter to True.

As this is a table calculation, the ‘filter’ is applied later in the order of operations, so information about the other rows in the table can be referenced. Filtering just by Period as a quick filter, is essentially a dimension filter and that happens earlier on in the process, meaning the data about the other rows would be inaccessible.

So we have all the fields, now build the cards.

Building the KPI Cards

On a new sheet, double click into Columns and type in MIN(0). Repeat this 2 more times. This gives us 3 axis to build each of the 3 cards.

On the All marks card, add Period to the Detail shelf. Show the pSelectedPeriod parameter. Add the Filter Selected Period to the Filter shelf and set to true (adjusting the table calc and resetting the filter value as required).

Change the mark type of the All marks card to shape and select a transparent shape (see here for more details).

On the first MIN(0) marks card, add Curr Sales, Prev Sales, Diff Sales %, Diff Sales Indicator +ve and Diff Sales Indicator -ve to the Label shelf. Remember to apply the table calc settings for all of the fields!

Adjust the text within the label, so it is formatted and positioned as required and then align middle centre.

On the middle MIN(0) marks card, do similar by adding the equivalent profit fields onto the Label shelf, and then repeat again for the bottom MIN(0) marks card, adding the quantity fields to the Label shelf.

On the All marks card, add Previous Period, Curr Sales, Curr Profit, Curr Qty Prev Sales, Prev Profit, Prov Qty, Diff Sales %, Diff Profit % and Diff Qty % to the Tooltip shelf (remember to set those table calc settings!) Then adjust the Tooltip to display the text as required. I used the ruler to shift the starting position, along with tabs (the tab keyboard button) to ‘try’ to get everything to align, and it works for most circumstances….

Finally format the KPI card

• Set the worksheet background colour to light grey
• Remove all gridlines, zero line, axis lines
• Set the column divider to be a thick white line
• Set the row divider to be a thick white line
• Hide the axis

Add the sheet onto a dashboard, and you should be done.

My published viz is here.

Happy vizzin’!

Donna