Therefore, all members of the Category dimension will be available within query that makes up the Grand Total row. The key to working with the Grand Total row is to consider the dimension(s) that is/are available to the Grand Total computation, as compared to what is available to the computations for the detail rows. In the graphic above, we can see that for the detail rows that there is a row for each Category is being used, whereas in the Grand Total row, there is no Category because it’s been removed from the overall level of detail in the query. This post offers two different calculations, one using MIN() and MAX() and the second using a table calculation with a duplicated dimension. Create the measure that returns the desired value for the Grand Total rows and another value for the data rows.Identify how and when Tableau is computing the grand total row.If we want to gain control over the value for the Grand Total, we have to do the following: You can see this the Tableau log files located in the …\My Tableau Repository\Logs directory.
For example, for AVG(Sales), Tableau will add COUNT(Sales) and SUM(Sales) to the query. If there’s not enough information available, Tableau revises the query to the data source to include additional fields so Tableau can compute the Grand Total. Tableau first computes the AVG(Sales) for each Category, then for the Grand Total row it takes Category out and computes AVG(Sales) across the entire data set. You can see what Tableau displays in the Grand Total row for each of the aggregations in the online help. On a more technical level, Tableau attempts to compute the Grand Total from the in-memory data cache. In essence, Tableau is combining two different views of the data set to create the final worksheet, as in this example using AVG(Sales): For Grand Totals for a row, Tableau takes out all the dimensions on the Columns Shelf and then performs the calculation. For Grand Totals for a column, Tableau takes out all the dimensions on the Rows Shelf and then performs the calculation. Tableau does not specifically compute Grand Totals and Subtotals as an aggregation of what is displayed instead, Tableau is performing the same calculation as the measure, but at a higher level of aggregation. If you want to boost your Tableau knowledge, sign up for the forums, go to Joe’s user page, and start reading posts he’s has contributed to, there are over 3,500 of them! The Goalįor this example, the goal is to get the Grand Total row to show a sum of the average sales per product category, instead of the overall average of the data set, which is the Superstore Sales sample data: Thanks to Joe Mako for his support in creating this, he provided the seeds for starting my own explorations into Grand Totals, and also feedback on this post. Having gone through the Top 10 Table Calculations and read the Table Calculations in Tableau 6 white paper should be a sufficient start. To follow this material, you should have an understanding of Tableau’s table calculations and partitioning.