Difference from Maximum of Running Totals in PowerPivot

Written by Jason Thomas on 22 October 2012.

Reposted from Jason Thomas blog with the author's permission.

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.

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.

Requirement

1. The running total of the points for each driver should be displayed race by race as shown below.

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

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]))

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

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

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

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?

Jason has been working with Microsoft BI tools since he joined the IT industry in 2006. Since then he has worked across SSAS, SSRS and SSIS for a large number of clients like Unilever, Imperial Tobacco, IATA, Schneider Electric, etc. He is currently working at MindTree Ltd, Bangalore (www.mindtree.com) and his personal blog site can be found at http://road-blogs.blogspot.com His blogs can also be found at the popular technical site BeyondRelational.com at http://beyondrelational.com/blogs/jason

Tags: dax, design

Difference from Maximum of Running Totals in PowerPivot

Similar Posts