SUM(), just like AVG(), MIN() etc. is an aggregation method, i.e. instruction to Tableau to add, average, etc. the values.
Say you have some sales data (Tableau's sample data) and you want to show sum of sales for each market. Tableau will do the SUM() in the background and show the 'totals'. But what if you also wanted to show what those sales in each market are as % of all sales? That's when you need TOTAL(), which will work together with SUM() to calculate the grand total and use it to figure out percentages. The formula for % would be:
SUM([Sales]) / TOTAL(SUM([Sales]))
In other words, on a Tableau sheet SUM() will only aggregate the data behind the cell, while TOTAL(SUM([x])) will aggregate all data on the sheet.
TOTAL() can be a bit confusing as it must be used with either SUM(), AVG(), etc., it is not just another kind of SUM() - it instructs Tableau to perform a table calculation, i.e. aggregate data behind many cells that are formed into a partition, but now we are starting to get into a complicated subject that is not that easy to understand. Tableau has some decent table calculation tutorials that are worth looking at, if you are interested.
total_example.png 6.9 KB
Thanks for that Dimitri. I think I am going to have to play with this for a while to ensure I have got my head around it.