KNOWLEDGE BASE

How to filter the records to show where a particular column/value accounts for half of the total


Published: 21 Jun 2022
Last Modified Date: 22 Jun 2022

Question

How to filter the records in which a specific column accounts for half of the total.

In the example below, the specific column is column "C". The values in column C is equal to or more than the Total.

User-added image

Environment

  • Tableau Desktop

Answer

In this example, we will use Sample Superstore and output only those records in which Office Supplies accounts for more than half of the total Quantity by state.

<STEP 1>
1. Connect to Sample Superstore.
2. Drag [Category] to Columns and [State] to Rows.
3. Drag [Quantity] to Text in the Mark.
4. Toolbar > Analytics > Totals > Show Row Grand Totals

<STEP 2>
5. Create a new calculated field as following.
   Name : Qty of Office Supplies
   Calculation : {FIXED [State] : SUM(IF [Category] = "Office Supplies" THEN [Quantity] END)}
   //Able to calculate Quantity of Office Supplies by state.
6. Create a new calculated field as following.
   Name : Qty by State
   Calculation : {FIXED [State]:SUM([Quantity])}
7. Create a new calculated field as following.
   Name : Filter
   Calculation : IF [Qty of Office Supplies] >= [Qty by State]/2
                        THEN TRUE
                        ELSE FALSE
                        END
8. Drag [Filter ] to Filter Pane > Check in True 
Did this article resolve the issue?