KNOWLEDGE BASE

Comparing Dimension Members


Published: 28 Dec 2016
Last Modified Date: 28 Dec 2016

Question

How to create a crosstab that shows for each dimension member, the other dimension members that have a lower measure value. For example, a crosstab that has a column for each category, and categories with lower sales are listed in each column.

Environment

Tableau Desktop 

Answer

Create a self-join to duplicate categories, de-duplicate measures with FIXED calculations, and then create a conditional calculation that will only display the expected categories.

The attached example workbook uses Superstore sample data to demonstrate the following instructions:

Step 1: Prepare your data
  1. Select the Data Source tab
  2. Drag a second copy of the "Orders" table into the connection window
  3. In the Join dialog, add the join clause Regions = Regions (Orders1)
Step 2: Create Calculated Fields
  1. Select a worksheet. 
  2. Select Analysis > Create Calculated field. Name the field "New Sales" and enter the following calculation: 
    { FIXED [Order ID], [Product ID] : SUM( [Sales] ) }
  3. Select Analysis > Create Calculated field. Name the field "New Sales (Orders1)" and enter the following calculation:
    { FIXED [Order ID (Orders1)], [Product ID (Orders1)] : SUM( [Sales (Orders1) ] ) }
  4. Select Analysis > Create Calculated field. Name the field "Categories with lower sales" and enter the following calculation:
    IF SUM( [New Sales] ) > SUM( [New Sales (Orders1)] )
    AND MIN( [Category ]) != MIN( [Category (Orders1)] )
    THEN MIN( [Category (Orders1)] )
    END

Step 3: Create the view

  1. Drag [Category] to Columns. 

  2. Drag [Category (Orders1)] to Detail. 
  3. Drag [Categories with lower sales] to Text.

Additional Information

Techniques used in the above example: 
  • The self-join duplicates categories, allowing us to list them twice. However, it also duplicates all the other fields in the data source. 
  • The join is made on a field with a higher level of detail than Category to ensure that Category is repeated as often as desired.
  • The calculated fields use LOD expressions to remove the unwanted duplication. 
Did this article resolve the issue?