KNOWLEDGE BASE

Aggregating at a Different Level than the Linking Field when Blending


Published: 06 Feb 2013
Last Modified Date: 20 Dec 2016

Question

How to aggregate at a different level than the linking field when using blended data.
 

Environment

Tableau Desktop

Answer

Option 1: Use a join instead of data blending.

For more information, see Join Your Data.

Option 2: Aggregate using WINDOW_SUM calculations. 

The below steps use the attached sample workbook and use Order ID as the linking field. 
  1. Drag the Order ID, Data source 1 - COUNTD, and Data source 2 - COUNTD fields to the Rows shelf. 
  2. Navigate to Analysis > Create Calculated Field, enter the following formula, then click OK:  
    window_sum([Data source 1 -  COUNTD])
  3. Navigate to Analysis > Create Calculated Field, enter the following formula, then click OK: 
    window_sum([Data source 2 -  COUNTD])
  4. Drag both of the WINDOW_SUM calculated fields to the Rows shelf.
  5. Right-click the WINDOW_SUM calculated fields on the Rows shelf and then select  Compute Using > Table down.
  6. Navigate to Analysis > Create Calculated Field, name the field Index,enter the following formula, then click OK:
    index()
  7. Drag the Index field to the Rows shelf.
  8. Right-click the Index field on the Rows shelf and then select Discrete.
  9. Right-click the Index field on the Rows shelf again and then select Compute Using > Table down.
  10. While pressing the Ctrl key, drag Index from the Rows shelf to the Filters shelf. Click None to clear all check boxes and then select the check box next to 1. Click OK.
  11. Right-click the Index field on the Rows shelf and then click Show Header.

Additional Information

Table calculations like INDEX() and WINDOW_SUM act on the measures in the view without affecting the underlying data, so they can be safely used to change the aggregation of a field when data blending.

For more information about INDEX() and WINDOW_SUM, see Table Calculation Functions
Did this article resolve the issue?