Showing Grand Totals with Blended Data

Published: 30 Jan 2013
Last modified date: 20 Jul 2023

Question

How to show the grand total when using blended data.

For example, when a calculated field in the primary data source references a field in the secondary data source, the results returned for grand totals may not reflect the expected number.

Environment

Tableau Desktop
Data blending

Answer

All three options are demonstrated in the attached workbook using the sample data set Superstore.

Option 1:

Join the data sources with a cross-database join instead of blending. See Join your Data for instructions.

Option 2: Create a calculation using WINDOW_SUM()

Drag the linking field(s) from the secondary data source to Details on the Marks card.
Create a calculated field with a calculation similar to the following:
If FIRST()=0 THEN WINDOW_SUM(<Calculated Field that references Field in Secondary Data Source>) END
Replace the calculated field that references a field in secondary data source with calculated field created in step 2
Right-click the replacement calculation on the shelf, and select Edit Table Calculation...
In the Table Calculation dialog, do the following:
1. Select Specific Dimensions
2. Check only the linking field(s)
Navigate to Analysis > Mark Stacking > Off
Optional: repeat steps 2-5 for all measures in the view

Note: The calculation must be created in the primary data source, referencing a field in the secondary data source.

Option 3: Create a calculation using INCLUDE

Modify the original calculation so that the non-additive part of the calculation is wrapped in a level of detail (LOD) INCLUDE expression. For example, the calculation

AVG([Discount])+ SUM([Category Costs].[Cost])

Could become:

SUM({ INCLUDE [Sub-Category] : AVG([Discount]) })
+ SUM([Category Costs].[Cost])

Note: This option will only work when the dimension(s) that determines the level of detail and the non-additive expression are in the same data source. Level of detail (LOD) expressions cannot include fields from multiple data sources.

Additional information

This issue only occurs for non-additive expressions, expressions that must be computed at specific level of detail before being totaled, when the total is set to Total using > Automatic. Automatic grand totals will return ignore the level of detail in the view and compute the calculations over the entire data set. For example, if the view shows daily averages, then the Automatic grand total will show the average over the entire data set in the view rather than the sum of all of the daily averages.

Calculations that use fields from secondary data sources can only show Automatic grand totals.