KNOWLEDGE BASE

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

Published: 21 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.

### Environment

• Tableau Desktop

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