Sort by Calculated Field in Tableau

While working on the product sales analysis, I created a report using Tableau. In this report, I also wanted to sort the records in increasing or decreasing order. The issue came when I tried to sort the calculated field using the sort button.

In Tableau, it is easy to sort the fields like Profit and Sales, but we cannot directly sort the calculations like Profit Sales ratio {SUM([Profit]) / SUM([Sales])}.

In this Tableau tutorial, I will explain how to sort values for a calculated field in Tableau for the table and non-table calculations.

Sort Values by Calculated Fields in Tableau

In Tableau, there are three ways to implement sorting for the calculated field values, which are as follows.

• Standard sort for non-table calculations
• Sorting with table calculations
• Dynamic sorting of calculated fields with a parameter

To show the sorting in the calculated fields, we will use the Tableau Superstore dataset. Now, connect the Tableau with the datasource and follow the methods below.

Method-1: Standard Sort for Non-Table Calculations in Tableau

In this method, we will see the sorting for the calculations that do not include any table calculations like RANK(), INDEX() and WINDOW_SUM().

In this sorting, we can sort the values for non-table calculations like Profit Ratio or Average Discount. In this example, we will sort the Sales for the Sub-Category according to the profit ratio.

1. To create the view, drag the Sub-Category to the Rows and Sales to the Columns.

2. Create a calculated field “Profit Margin” using the formula below. Based on this field, we will sort the sales for the sub-category.

SUM([Profit])/SUM([Sales])

3. For sorting, click on the Sub-Category dimension dropdown and select the Sort option.

4. In the sort setup, select Sort By as Field, and then select the Sort Order. Then, in the Field Name, select the calculated field by which we need to sort values.

Now, we can see the sales values sorted by the calculated field Profit Ratio for the Sub-Category dimension.

This way, we can sort the values for a calculated field in Tableau by following the steps above.

Method-2: Apply Sorting For Calculated Fields with Table Calculation in Tableau

In this method, we will sort the calculated fields with quick table calculations like RANK(), INDEX(), and WINDOW_SUM(). In this example, we will create a calculated field using the WINDOW_SUM() table calculation.

In Tableau, we cannot directly sort the calculated field that has table calculations. In Tableau, table calculations are calculated after Tableau does the normal aggregations, so they won’t appear in the standard Sort dialogue.

In this example, we will create a calculated field that will calculate the percentage each row contributes to the whole sales, and then we will use that as a hidden field to control the sorting.

1. Create a calculated field “% of Sort Order” using the formula below.

SUM([Sales]) / WINDOW_SUM(SUM([Sales]))

In case you want to sort values in ascending order, then use the formula below.

-1* (SUM([Sales]) / WINDOW_SUM(SUM([Sales])))

2. Now, to create the chart view, add ‘Sub-category‘ to the rows and ‘Sales‘ to the columns.

3. Drag the calculated field to the Columns and convert it to a discrete field. For conversion, click on the field in columns and select Discrete.

4. Move the calculated field to the left of the Sub-Category dimension. After this, we will see the sales of Sub-Category by the order of their share percentage in total sales.

5. To remove the column of the calculated field, click on the field in the Columns and uncheck Show Header.

This way, we can use a calculated field as a key to sort the values in Tableau by following this method.

Method-3: Dynamic Sorting of Calculated Fields With a Parameter

In this example, we will implement dynamic sorting for the calculated fields with a parameter. Using this sorting, we can dynamically change the sorting measures such as Sales, Discount and Profit.

Now, follow the steps below to implement dynamic sorting using a calculated field in Tableau.

1. First, create a parameter through which we can change the dimensions from Sales, Profit and Discount. Now, create the parameter with the details below.
   • Enter the parameter name as Sort by Parameter.
   • Select the Data type as String.
   • In the Allowable values, select List and enter the Values as Sales, Profit and Discount.
   • At last, click on the OK button.

2. Using this parameter, create a calculated field with the formula below. This formula will change the sort dimension as we change the value in the parameter.

CASE [Sort by Parameter]  
WHEN "Sales" THEN SUM([Sales])  
WHEN "Profit" THEN SUM([Profit])  
WHEN "Discount" THEN SUM([Discount])  
END

3. To create the view, add the Sub-Category and calculated field to the Rows. After this, add a measure such as Sales to the Columns.

4. Now, change the calculated field from Continuous to Discrete.

5. Shift the calculated field to the left side of the Sub-Category in Columns. To remove the column from the table, click on the calculated field and uncheck ‘Show header‘.

6. To make the parameter values visible on the worksheet, go to the Data pane, click on the parameter and select Show parameter.

7. Now, as we change the values in the parameter, the sort order will display the value for the selected measure.

This way, we can dynamically sort the values in Tableau using the calculated fields.

Conclusion

In this Tableau tutorial, we have learned how to sort calculated fields in Tableau . In the above example, we have sorted values for non-table calculations like Profit Ratio; we can use the standard sort option.

For table calculations such as % of Total Sales, we created a calculated field and used it as a hidden discrete header to control the sort order. At last, we implemented dynamic sorting with parameters, which allows us to choose whether to sort by Sales, Profit, or Discount. By applying these methods, we can easily handle sorting for both simple and advanced calculations in Tableau.

You may also like to read:

• Order of Operations in Tableau
• Create Groups in Tableau
• Level of Detail Expressions in Tableau
• Export Tableau Report in PDF

Bijay Kumar

I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.