Home > Tableau > Show the total for selected and unselected values

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 the unselected values must be calculated before the filter is applied.

Unselected Total Screenshot

(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. FIXED is calculated before the filters take affect, 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 just the sum of sales of those items selected in the filter. SUM([Sales]).

Unselected sales is the Total Sales minus the selected sales. SUM({FIXED : SUM([Sales])}) – SUM([Sales])

To put the chart together use Measure Names and Measure Values to create the bars. Place Measure Values to the Label, colour by Measure Names and to top it off put the total sales in the title.

Categories: Tableau Tags:
  1. No comments yet.
  1. No trackbacks yet.