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

# Calculations based on two columns in a row

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:

 Date Route Month Day Start End Price 1/4/2012 London - Berlin 1 Monday London Berlin 55 6/4/2012 Paris - Warsaw 6 Wednesday Paris Warsaw 89 9/3/2012 London - Berlin 9 Friday Berlin London 55 5/2/2012 Paris - Warsaw 5 Saturday Paris Warsaw 89 2/2/2012 Berlin - Rome 2 Sunday Berlin Rome 78 6/9/2012 Berlin - Rome 6 Tuesday Rome Berlin 78 1/9/2012 London - Berlin 1 Tuesday London Berlin 55 9/5/2012 London - Berlin 9 Wednesday Berlin London 55 1/9/2012 Berlin - Rome 1 Monday Rome Berlin 78

After the calculations, data should look something like this:

 Station Revenue London 110 (55 as the first station and 55 as the last station) Berlin 227 (94 as the first and 133 as the last) Paris 89 (89 as the first and 0 as the last) Warsaw 89 (0 as the first and 89 as the last) Rome 117 (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

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.