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.