Using Level Of Detail Calcs to Work out % of Sales on Different Levels of Aggregation

by Kofi Bonsu

For this example I’ll be using the Sample – Superstore dataset.

There is a hierarchy present in the data, with the highest level of aggregation being United States, the individual States are the lower level of aggregation, with the cities and Postal Codes the subsequent lower levels of the hierarchy. The States are broken up into 4 Regions: Central, East, South and West.

The States can be coloured by their Sales value by putting the Sales measure on the colour mark.

Tableau colours the States from lowest value to highest value using a sequential colour palette.

To work out the percentage of Sales per State for the whole of the United States drag the Sales pill on to the view.

Right click on the Sales pill and select Add Table Calculation…

Then select Percent of Total, select Specific Dimensions and make sure the State and Country tick boxes are selected.

This is what the view is like once that’s done.

To see the percentage of sales in a Region, simply put the Region pill on the Filter and select a Region. The view updates so only the States in that Region are shown and they have the total percent of sales for that particular Region showing.

To get the percentage of sales for a State in a Region while also seeing the
percentage of sales for a State in the US, a fixed Level of Detail (LOD) calculation will need to be used.

A {FIXED} LOD fixes the aggregation of the measure in the calc at the level of the dimension regardless of whether it’s in the view.

The formula to fix the Sales at a Country level aggregation is:

{FIXED [Country]: sum([Sales])}

To work out the percentage of sales per State by the Country, this calculation is needed:

SUM([Sales])/ SUM({ FIXED[Country]: SUM([Sales])})

It’s saying to add together the sales in a State and divide that by the sum of sales in the Country.

Save the calculation and put it on the Label mark so this is the view.

% of Sales by State in the East Region (top), % of Sales by State in the Country (bottom)

© 2022 The Information Lab Ltd. All rights reserved.