KNOWLEDGE BASE

## Comparing Dimension Members

Published: 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

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:

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.

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.