Comparing Dimension Members

Published: 28 Dec 2016
Last Modified Date: 25 Sep 2019

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.

Discuss this article... Feedback Forum