Comparing Dimension Members

Published: 28 Dec 2016
Last Modified Date: 17 Jul 2018

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

Select the Data Source tab
Drag a second copy of the "Orders" table into the connection window
In the Join dialog, add the join clause Regions = Regions (Orders1)

Step 2: Create Calculated Fields

Select a worksheet.
Select Analysis > Create Calculated field. Name the field "New Sales" and enter the following calculation:
{ FIXED [Order ID], [Product ID] : SUM( [Sales] ) }
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) ] ) }
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

Drag [Category] to Columns.
Drag [Category (Orders1)] to Detail.
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.