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