1 Reply Latest reply on Jul 24, 2018 4:52 PM by Okechukwu Ossai

# Sum units based on origin and/or destination state

Hello,

I am trying to sum units when the origin and/or destination state is the same. For example:

OriginDestinationUnits
IAOK100
OKTX50
KSIA

30

IAIA10

I want to be able to filter by state and see the total amount of units.

Desired Outcome:

IA - 140

OK - 150

TX - 50

KS - 30

I have tired creating a calculated IF statement and the using it as a filter but it doesn't sum the units.

IF [Shipment Origin State] = 'IA' OR [Shipment Destination State] = 'IA' THEN 'IA'

ELSEIF  [Shipment Origin State] = 'OK' OR  [Shipment Destination State] = 'OK' THEN 'OK'

ELSEIF [Shipment Origin State] = 'LA' OR [Shipment Destination State] = 'LA' THEN 'LA'

ELSEIF [Shipment Origin State] = 'UT' OR [Shipment Destination State] = 'UT' THEN 'UT'

• ###### 1. Re: Sum units based on origin and/or destination state

HI Kimberley,

Your dataset requires a slight restructuring. The solution below uses a cross join approach and assumes you are using an Excel datasource and Tableau 10.2 version or newer.

Create a new table with a complete list of the states and cross join this to the original dataset using a join calculation 1 = 1. I have attached both the Tableau workbook and modified Excel datasource.

Assuming you are using an Excel datasource, then you can create a new table with a complete list of the states and cross join this to original dataset using a join calculation 1 = 1.

Cross joins often produce duplicates. So, it is ideal if you don't have massive dataset.

Create calculated field [Row Filter]

IF [State] = [Destination] OR [State] = [Origin] THEN 'Keep' ELSE 'Exclude' END

Add [Row Filter] to the filter shelf and set to 'Keep'.

Hope this helps.

Ossai