Last Modified Date: 25 Sep 2019
Environment
Tableau DesktopAnswer
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)
- 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
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Continue Searching
Knowledge Base
Community
Product Help
Training and Tutorials