Indexing in PowerPivot

[cajste]

Hi,

I have a table in PowerPivot of some 500 000 rows with different vehicle departure times, including deviations from schedule. There are a number of different route IDs (PathIdent), each route has a number of destinations (Location) where the vehicle stops. For each route there are also a number of voyages, maybe but not for sure with the same departure time (Departure). There are no voyage numbers but all rows for each voyage has the same planned departure date(Departure).

I would like to show deviation per route and location over time to determine where in the schedule we lose time. To do that I need to index each destination within each route so that I can sort them. The question – how do I do that in a PowerPivot table looking like the example below?

Brgds,
Caj

[TABLE="class: cms_table, width: 848"]
<tbody>[TR]
[TD]PathIdent
[/TD]
[TD]Departure
[/TD]
[TD]PathDepLoc
[/TD]
[TD]Location
[/TD]
[TD]LocationStatus
[/TD]
[TD]TimeAtLocation
[/TD]
[TD]Deviation
[/TD]
[/TR]
[TR]
[TD]41761
[/TD]
[TD]2012-05-11 18:00
[/TD]
[TD]HTG
[/TD]
[TD]Ros
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 18:27
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41761
[/TD]
[TD]2012-05-11 18:00
[/TD]
[TD]HTG
[/TD]
[TD]Tsg
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 19:03
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41761
[/TD]
[TD]2012-05-11 18:00
[/TD]
[TD]HTG
[/TD]
[TD]Åd
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 19:29
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41615
[/TD]
[TD]2012-05-11 18:00
[/TD]
[TD]KIL
[/TD]
[TD]Gms
[/TD]
[TD]Arrival
[/TD]
[TD]2012-05-11 18:19
[/TD]
[TD]-10
[/TD]
[/TR]
[TR]
[TD]41615
[/TD]
[TD]2012-05-11 18:00
[/TD]
[TD]KIL
[/TD]
[TD]Kil
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 18:00
[/TD]
[TD]-12
[/TD]
[/TR]
[TR]
[TD]41615
[/TD]
[TD]2012-05-11 18:00
[/TD]
[TD]KIL
[/TD]
[TD]El
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 18:11
[/TD]
[TD]-10
[/TD]
[/TR]
[TR]
[TD]41761
[/TD]
[TD]2012-05-11 18:00
[/TD]
[TD]HTG
[/TD]
[TD]Htg
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 18:00
[/TD]
[TD]-58
[/TD]
[/TR]
[TR]
[TD]41761
[/TD]
[TD]2012-05-11 18:00
[/TD]
[TD]HTG
[/TD]
[TD]Fsm
[/TD]
[TD]Arrival
[/TD]
[TD]2012-05-11 19:59
[/TD]
[TD]17
[/TD]
[/TR]
[TR]
[TD]41720
[/TD]
[TD]2012-05-11 18:26
[/TD]
[TD]SUC
[/TD]
[TD]Töv
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 18:37
[/TD]
[TD]-168
[/TD]
[/TR]
[TR]
[TD]41720
[/TD]
[TD]2012-05-11 18:26
[/TD]
[TD]SUC
[/TD]
[TD]Vm
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 18:47
[/TD]
[TD]-172
[/TD]
[/TR]
[TR]
[TD]41720
[/TD]
[TD]2012-05-11 18:26
[/TD]
[TD]SUC
[/TD]
[TD]To
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 19:19
[/TD]
[TD]-173
[/TD]
[/TR]
[TR]
[TD]41720
[/TD]
[TD]2012-05-11 18:26
[/TD]
[TD]SUC
[/TD]
[TD]Nsö
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 18:54
[/TD]
[TD]-172
[/TD]
[/TR]
[TR]
[TD]41720
[/TD]
[TD]2012-05-11 18:26
[/TD]
[TD]SUC
[/TD]
[TD]Åg
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 19:45
[/TD]
[TD]-170
[/TD]
[/TR]
[TR]
[TD]41720
[/TD]
[TD]2012-05-11 18:26
[/TD]
[TD]SUC
[/TD]
[TD]Visk
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 19:16
[/TD]
[TD]-174
[/TD]
[/TR]
[TR]
[TD]41720
[/TD]
[TD]2012-05-11 18:26
[/TD]
[TD]SUC
[/TD]
[TD]Åggb
[/TD]
[TD]Arrival
[/TD]
[TD]2012-05-11 19:48
[/TD]
[TD]-170
[/TD]
[/TR]
[TR]
[TD]41720
[/TD]
[TD]2012-05-11 18:26
[/TD]
[TD]SUC
[/TD]
[TD]Std
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 19:07
[/TD]
[TD]-173
[/TD]
[/TR]
[TR]
[TD]41720
[/TD]
[TD]2012-05-11 18:26
[/TD]
[TD]SUC
[/TD]
[TD]Ft
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 19:26
[/TD]
[TD]-174
[/TD]
[/TR]
[TR]
[TD]41720
[/TD]
[TD]2012-05-11 18:26
[/TD]
[TD]SUC
[/TD]
[TD]Jbg
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 19:30
[/TD]
[TD]-173
[/TD]
[/TR]
[TR]
[TD]41720
[/TD]
[TD]2012-05-11 18:26
[/TD]
[TD]SUC
[/TD]
[TD]Ei
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 19:35
[/TD]
[TD]-172
[/TD]
[/TR]
[TR]
[TD]41720
[/TD]
[TD]2012-05-11 18:26
[/TD]
[TD]SUC
[/TD]
[TD]Suc
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 18:26
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41720
[/TD]
[TD]2012-05-11 18:26
[/TD]
[TD]SUC
[/TD]
[TD]Vm
[/TD]
[TD]Arrival
[/TD]
[TD]2012-05-11 18:42
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41674
[/TD]
[TD]2012-05-11 20:15
[/TD]
[TD]GMS
[/TD]
[TD]Gms
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 20:15
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41674
[/TD]
[TD]2012-05-11 20:15
[/TD]
[TD]GMS
[/TD]
[TD]El
[/TD]
[TD]Departure
[/TD]
[TD]2012-05-11 20:24
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41674
[/TD]
[TD]2012-05-11 20:15
[/TD]
[TD]GMS
[/TD]
[TD]Kil
[/TD]
[TD]Arrival
[/TD]
[TD]2012-05-11 20:32
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

​

 

[miguel.escobar]

Hey Caj,

What if you drag and drop the
PathIdent
to the row columns and then create a measure that will summarize the deviation? would this approach do the job?

 

[cajste]

Hi!

Thanks for your answer, Pathident is not doing the job unfortunately. PathIdent will be the page filter or a slicer in the report. I would like to plot a grapgh on data like below and show where we lose or gain time in the schedules and if needed breakt it down to each voyage. To be able to do that I need to the order of the locations, but that I can't determine right now. I was hopping I could use the hour and minute part of the Planned Departure field to do that (not shown above), so I created a column with only that information. It works for one route where the departure is always the same time, but the other routes have variations in the departues and then that method does not work.

So somehow I need to index the order of the locations based on the field TimeAtLocation for each Departure value and PathIdent since two different voyages on two different PathIdents can have the same time. That way location Ros would always = 1, Tsg=2 and Åd=3 and so on and I can calculate average deviation per month and PathIdent and drill down to specifik voyages.

Hope that made it a little bit clearer... Appriciate any help!

Brgds,
Caj

[TABLE="width: 344"]
<tbody>[TR]
[TD]Location[/TD]
[TD]January Avg[/TD]
[TD]February Avg[/TD]
[TD]March Avg[/TD]
[/TR]
[TR]
[TD]Ros[/TD]
[TD="align: right"]-4[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]-8[/TD]
[/TR]
[TR]
[TD]Tsg[/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-11[/TD]
[TD="align: right"]-9[/TD]
[/TR]
[TR]
[TD]Åd[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]-6[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[Laurent C]

Do I understand your data right?

PathIdent = 41761
Actual trip : HTG -> ROS -> TSG -> AD
PathIdent = 41615
Actual trip : KIL -> KIL -> EL -> GMS (with some problem at the start)

So, you would like to be able to identify whether there is generally a problem going from ROS to TSG?

In that case, you might want to consider some variation of the Order/OrderDetail model.

Basically that means, having a table for your route, and a table detailing the steps for each route::
Path { ( PathIdent, Departure, PathDepLoc )}

PathEvent { (PathIdent, StepId, DepartureLocation, ArrivalLocation, DepartureTime, ArrivalTime, Deviation, ... ) }

 

[cajste]

Yes, you understand it correctly and that is exactly what I want to show. I'll look at your sugestion to see if can get it to work, thanks!

Brgds
Caj