KNOWLEDGE BASE

Ordering Dimension By Total of Sum of Measure from Secondary (Blended) Data Source


Published: 06 Jan 2016
Last Modified Date: 12 Sep 2016

Question

How to sum the totals of the two displayed years and order the products in the list by this sum.

Environment

Tableau Desktop 9.0 and later

Answer

In order to achieve the desired view and functionality please follow the steps detailed below:

I) Build the View

1. In a new sheet using the Superstore Data Source
2. Drag YEAR(Order Date) from Dimensions onto Columns
3. Drag State from Dimensions onto Columns
4. Change to the Extract of Coffee Chain Data Source (Ensure to set Relationship on State and YEAR(Date)) 
5. Drag Profit from Measures onto Columns (Exclude 2011 and 2014)

II) Create the field with values to sort

6. Right-click on Profit in Measures and Select Create > Calculated Field
7. Enter the following:
Name: Profit Sorting
Formula: SUM({FIXED [State]: SUM([Profit])})

III) Create the rank using the field to sort

8. Change to the Superstore Data Source
9. In Measure right-click and Select Create > Calculated Field
10. Enter the following:
Name: RANK
Formula: RANK_DENSE([Sample - Coffee Chain (Access)].[Profit Sorting]) 

IV) Configure rank

11. Drag RANK onto Rows
12. Right-click on RANK and Select Discrete
13 Place RANK to the most left position on Rows
14. Right-click on RANK on Rows and Select Sort
15. Set to Manual and Bring Null to the bottom of the list
15b. Or Exclude the Null
Did this article resolve the issue?