Skip navigation

Totals and subtotals over displayed marks instead of separate calc (aka make subtotals work like Excel)

score 439
You have not voted. Active

[added 2015-01-25] In Tableau 8.1 two-pass totals were added, however they don't cover all situations, they only work for regular aggregate measures coming from a single data source (primary or secondary). What remains to be added to fully implement this idea are support for the following kinds of calculations:

 

- blended calculations that use measures from more than one data source (See this comment below for more details).

- calculations using ATTR()

- table calculations

 

[added 2015-06-16] One workaround is that the the new Level of Detail expressions on version 9 make it possible to avoid some situations where self-blends, ATTR(), and/or table calculations were used that would block the use of two-pass totals. The results of LOD expressions are available as regular aggregate measures to the view so two-pass totals will work in those situations.

 

What follows is the text of the original idea:

 

Currently, the default/automatic totals and subtotals are generated as separate calculations at a level of detail appropriate to the total or subtotal. So, for example, if the aggregate is AVG(Sales) per Department then the subtotal will show AVG(Sales) across the data source:

 

Screen Shot 2013-05-09 at 5.39.43 PM.PNG

 

However, a common request (and the subject of many forum posts) is to be able to have the Grand Total computed over the displayed marks, for example the sum of the average sales or average of averages. This is essentially what Excel does when we either use the built-in subtotals or manually add them.

 

In Tableau, the existing separate computation at a higher level of detail can also cause problems when the displayed data is coming from a blend and the total or subtotal at a higher level of aggregation causes the blend to return incorrect results and/or no results at all. Here's a demonstration of this from a recent forum question:

http://community.tableausoftware.com/message/177225#177225

 

I think it would be useful if there could be an option for an alternate calculation where totals and subtotals would be calculated over the displayed marks in the view, for example having options on the context menu of the measure pill and/or the Grand Total row:

 

grand total idea.png

 

[Updated 2013-05-09 per Mark's feedback.]

[Updated 2015-01-25 to be clear on what needs to be done.]

[Updated 2015-06-16 to add note on LOD expressions and re-order text to be more clear.]

Comments