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

    Sum units based on origin and/or destination state

    Kimberly Gerstner

      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
          Okechukwu Ossai

          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