Pages

Thursday, October 18, 2012

Difference from Maximum of Running Totals in PowerPivot

I don’t know how many of you are Formula1 fans here, but this season is pretty good. Sebastian Vettel and Fernando Alonso are going neck and neck for the title and there are just 4 races remaining. I try to follow F1 as much as I can, but had missed watching the last couple of races and there were some pivotal moments as Vettel took the lead from Alonso. I really missed knowing how they were doing at the end of each race and how far the other drivers were from the race leader. That is when I decided to relive the experience by mocking up the data in PowerPivot.
PowerPivot Running Total Max
So what I want to do is to get a running total of the points for each driver across the races., so that I can see how each driver was doing throughout the season. Also, it would give me a better idea of their performance if I could get the difference of the points for each driver from the race leader at the end of that race. Interesting huh?
 
Source
The source for our exercise can be downloaded from here and contains the following data:-
1. RaceFact
This table contains the positions for each driver across all the races in this season. The columns are Driver, Race, Position and a flag whether they crashed or not.
2. RaceOrder
This table contains all the completed races in this season and the order in which they happened. The columns are Race and Order.
3. Points
This table contains the points for each position. The columns are Position and Points.
4. Driver
This table contains the list of all drivers and just has one column - Driver.
Race data model
 
Requirement
1. The running total of the points for each driver should be displayed race by race as shown below.
Running Total of Points

2. The difference of each driver from the leader (as of that race) should be displayed.

Diff from max of Running Total

Implementation

1. Build the PowerPivot model for the RaceData as shown in the diagram above. For simplicity, I have already created the model in the source file available for download here.

2. For creating the running total across the races, we will be following the technique detailed in my previous blog. For that, create 2 calculated columns in the RaceFact table called Points and Order respectively.

=calculate(sum(Points[Points]))

=calculate(sum(Races[Order]))

calc columns

Notice that the foreign keys in the fact table (Driver, Race, Position) are hidden as it is a best practice to hide the foreign keys. This way, the users will not get confused whether to use the columns in the master tables or the fact tables.

3. For finding the running total, we can create a new measure with the below formula

RT_Points:=calculate(sum(RaceFact[Points]), filter(Allexcept(RaceFact,Driver[Driver]), RaceFact[Order]<=Max(RaceFact[Order])))

When this measure is used against the drivers on rows and races on columns, it will display the running total of the points for each driver across the races.

4. Now for getting the difference of each driver from the leader, we need a measure which will give us the points of the race leader at the end of every race. This measure would be equal to the max of the running total that we just created and that should be the same for all drivers for a particular race. Create the measure with the DAX formula given below

MaxRT_Points:=calculate(maxx(values(Driver[Driver]), [RT_Points]), All(Driver[Driver]))

The result is shown below

MaxRTPoints

5. Now all we have to do to get the final requirement is to subtract the MaxRT_Points measure from the RT_Points measure as shown below

DiffFromLeader:=[RT_Points] - [MaxRT_Points]

The result is shown below

Diff from MaxRTPoints

6. With a bit of formatting and adding a chart for the top 6 drivers, I can now clearly see how the battle for the top place has been going

Chart

The final version of the PowerPivot file can be downloaded from here. It is interesting to see that Fernando Alonso has been the most consistent driver, as can be seen from the above chart, even though he is in second place. With this information, I am going to bet my money on Alonso winning the Drivers championship (and not because I am a biased Ferrari fan :P). What do you reckon?

4 comments: