The #WOW2023 challenge this week was set by guest challenger Valerija Kirjackaja, who asked us to recreate a tabular view of data.

Model the data

The requirement involved both the use of the Superstore data set and a custom Target data set, so these need to be combined. In the data pane, relate the two data sets together applying the fields below in the screen shots as the relationship fields (not you’ll need to create a relationship calculation to build the 3rd relationship.

Building the table

The table displays Category & Sub-Category along with a 3rd dimension which will differ depending on user selection. So we need to enable this choice.

pBreakdown

string parameter containing a list of options, defaulted to Ship Mode

Then create a calculated field to determine the actual field to show based on the parameter selection

Breakdown Dimension

CASE [pBreakdown]
WHEN ‘Segment’ THEN [Segment]
WHEN ‘Region’ THEN [Region]
WHEN ‘Ship Mode’ THEN [Ship Mode]
END

The user will also need to select a month. I chose to use a calculated field and parameter to drive this.

Month

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

and this then feeds the parameter

pMonth

date parameter, formatted to custom date format of mmmm yyyy (to display September 2023). The list is populated when the workbook opens from the Month calculated field created above. Default date is 01 Sept 2023.

The report will need to be filtered based on the date selected in the parameter, so create

Filter Date

[Month] = [pMonth]

On a new sheet, add Category & Sub-Category (from the Orders data set) and Breakdown Dimension to Rows. Show the parameters. Add Filter Date to the Filter shelf and set to True.

Now, the table shows some conditional formatting, which is a clue that we’re not going to building this table in the basic way. Instead we’ll be using what I refer to as ‘fake axis’ to define placeholder columns, which we can then use to have more flexibility on how the data is displayed.

Double click into the Columns shelf and type MIN(0.0).

Change the mark type to Shape and use a transparent shape (see this blog for more information on doing this). Add Sales to the Label shelf and align left. Add all subtotals (Analysis > Totals > Add All Subtotals) and then set the all to display at the top (Analysis > Totals > Column Totals to Top).

Adjust the formatting, so the row dividers are at the highest level

and the row banding is also at the total pane/header level

Type in another instance of MIN(0.0). This will create a second marks card. Adjust the Sales pill and apply a quick table calculation of percent of total. Set to compute using the Breakdown Dimension field. Format the field to % with 0dp.

To stop the % displaying on the Total rows, click the table calc field and select Total using > Hide

Add another instance of MIN(0.0) and this time add Sales Target value from the Sheet1 data set to the Label shelf.

You can see the target values fill down against the Breakdown Display rows, but we don’t want this.

So to work out whether we’re on a Total row or not, we’re going to make use of the SIZE() table calculation.

We want to count the number of Breakdown Dimension rows being shown.

Count Breakdown Dimension Rows

SIZE()

change this to be discrete, add this to Rows and adjust the table calculation so it is compute using the Breakdown Dimension field. You can see the value against each row represents the number of (in this case) different Ship Mode values.

What isn’t visible is the fact that the SIZE() values against the Total rows are actually 1. Move the Count Breakdown Dimension Rows to the Tooltip shelf on the All marks card. If you now hover over the rows of data, the tooltip should display 1 against this field when it’s a total row and the count otherwise. We’ll use this to identify the Total rows.

This is a common technique that is used. Note though, that if the ‘variable’ you’re counting over only has 1 value, then any logic based on this will apply to that row too (see the Copiers row for Ship Mode & Sept 2023).

So to display the Target Sales as we want, we need

Sales Target to Display

IF [Count Breakdown Dimension Rows] = 1 THEN SUM([Sales Target]) END

ie only show the value of the target on the total rows (or when there’s only 1 value in the dimension being counted).

Replace the Sales Target pill on the 3rd MIN(0.0) marks card with this Sales Target to Display field. Make sure the table calculation is set to compute using Breakdown Dimension

Now we need the variance

Variance

(SUM(Sales)-[Sales Target to Display])/SUM([Sales])

Format this to ▲0.0%;▼0.0%

and we need to identify if it’s +ve or -ve

Variance is +ve

[Variance] >=0

Create another instance of MIN(0.0). Add the Variance field to the Label shelf and verify it is computing by Breakdown Dimension

Add Variance is +ve to the Colour shelf. Adjust the colours, then on the Label shelf, set the font to match mark colour.

To change the ‘Total’ labels to ‘All’, right click on one of the labels, and change the label in the left hand pane.

Finally, adjust the formatting – I set the font throughout to be a darker colour, set the All labels to be bold, removed all gridlines, zero lines and column dividers, hid the MIN(0) axes and also hide field labels for rows. Turn off tooltips for the whole table.

Building the dashboard

I then added the sheet to a dashboard, and placed it within a vertical container. Above the vertical container, I used a horizontal container in which I added text boxes for the column headers. The text box for the variable dimension, referenced the pBreakdown parameter value. I also then used a floating blank set to a height of 2px with a background colour of grey to give the appearance of a line above the column headings.

In the dashboard heading, refer to the pMonth parameter to display the date.

Once published, I did have to tweak the width of the heading text boxes and the position of the floating line within the web edit view.

My published viz is here.

I essentially ‘faked’ the table header row but I still only used 1 sheet to build the table and I was able to dynamically change the title of the dimension column, so hey I hit the brief 🙂 If you want to understand how to build the header row into the actual table itself, then check out Rosario Gauna’s blog.

Happy vizzin’!

Donna