Show the total for selected and unselected values

//

Recently I came across a chart that showed the total for both the selected and unselected values. It’s actually very simple to create but did take a bit of thought to begin.

We want the values NOT selected to remain within the view. A standard filter removes the values completely.

Therefore calculate the unselected values BEFORE applying the filter.

Tableau bar chart showing selected and unselected items in the bars

(Image included as the Tableau Public version doesn’t open for users, especially in organisations with strictly controlled IT)

The answer is LOD calculations, specifically the FIXED calculation.

Use a FIXED LOD to calculate before filters

FIXED calculations are calculated before standard filtering, therefore any standard filter selections make no difference. (Note I’m ignoring both Context and Table Calculation filters, that just complicates matters.)

Create a calculation to ‘fix’ the Total Sales:

{FIXED : SUM([Sales])}

The bar Selected Sales is simple – it is the sum of sales of the selected items.

Selected Sales: SUM([Sales])

Unselected sales is the Total Sales minus the selected sales.

Unselected Sales: SUM({FIXED : SUM([Sales])}) - SUM([Sales])

The new Selected Sales and Unselected Sales fields should appear in the Measures list.

To create the chart using the new fields, use Measure Names and Measure Values to create the bars. Place Measure Values to the Label and colour by Measure Names. Filter Measure Names to only keep the two new fields. To finish put the total sales in the title.

Leave a Comment