1 Reply Latest reply on Nov 22, 2013 1:32 PM by Mark Holtz

    Calculations based on two columns in a row

    Justas Jarusevicius

      Hello everyone

       

      I'm currently working on a dashboard that analyzes railway passenger transportation routes and there is one problem I seem to be facing. Each row in my data file represents a single ticket that was sold. It has dimensions like route, date, starting station, ending station, ticket price etc. What is interesting for me, is the revenue each station is making. It is easy to calculate the revenue for all the stations, where passengers get on the train or get off the train but I would like to combine these, so that the starting station receives half of the route's revenue and the ending station receives the other half. To illustrate:

       

      The ticket file:

      DateRouteMonthDayStartEndPrice
      1/4/2012London - Berlin1MondayLondonBerlin55
      6/4/2012Paris - Warsaw6WednesdayParisWarsaw89
      9/3/2012London - Berlin9FridayBerlinLondon55
      5/2/2012Paris - Warsaw5SaturdayParisWarsaw89
      2/2/2012Berlin - Rome2SundayBerlinRome78
      6/9/2012Berlin - Rome6TuesdayRomeBerlin78
      1/9/2012London - Berlin1TuesdayLondonBerlin55
      9/5/2012London - Berlin9WednesdayBerlinLondon55
      1/9/2012Berlin - Rome1MondayRomeBerlin78


      After the calculations, data should look something like this:

      StationRevenue

      London

      110(55 as the first station and 55 as the last station)
      Berlin227(94 as the first and 133 as the last)
      Paris89(89 as the first and 0 as the last)
      Warsaw89(0 as the first and 89 as the last)
      Rome117(78 as the first and 39 as the last)

       

      Is it possible to make calculations like this on Tableau? I can make summaries on Excel and import them to Tableau but this means that I'll be losing the detail of data and flexibility to filter it in all the dimensions necessary.

       

      Any ideas would be very helpful. Thanks.

        • 1. Re: Calculations based on two columns in a row
          Mark Holtz

          Well, there are a few ways you could go about this.

          One way would be to re-shape your underlying data to exist instead of the full-ride-per-line level to have each ticket split into 2 rows and have an additional dimension field specify "Departure" or "Arrival."

           

          Within Tableau, if you don't have TOO many stations, another way would be to create a calculated field to act as the revenue measure for each Station.

          e.g., London Revenue =

          IF [Start] = 'London' OR [End] = 'London'
          THEN 0.5 * [Price]
          ELSE 0
          END

           

          Repeat for each station, swapping the criteria for Berlin, Paris, etc.