KNOWLEDGE BASE

Showing Grand Totals with Blended Data


Published: 30 Jan 2013
Last Modified Date: 15 Jun 2018

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 Quick Start: Combine Tables Using Cross-Database Joins for instructions.

Option 2: Create a calculation using WINDOW_SUM()

  1. Drag the linking field(s) from the secondary data source to Details on the Marks card.
  2. 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
  3. Replace the calculated field that references a field in secondary data source with calculated field created in step 2
  4. Right-click the replacement calculation on the shelf, and select Edit Table Calculation...
  5. In the Table Calculation dialog, do the following:
    1. Select Specific Dimensions
    2. Check only the linking field(s)
  6. Navigate to Analysis > Mark Stacking > Off
  7. Optional: repeat steps 2-5 for all measures in the view
     
Note: In 9.3 and earlier versions, substitute the following for step 5: 
  1. Select Compute Using: Advanced... 
    In the Advanced dialog box, choose Addressing: <Linking field from secondary data source>, then click OK. 

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

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

Could become:

{ 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.
 
Did this article resolve the issue?