PowerPivot

As a Business Intelligence professional, I find it really fascin(/frustr)ating working with Excel users. You get exposed to a completely different way of thinking when you work with them, not to say that they are extremely smart and demanding. From the moment I started working with PowerPivot and interacting with Excel users, I knew it was not going to be easy tackling their questions and requirements. You simply cant escape by saying that the required feature is not available because they will push you till you find a workaround. This was the case when one of my users came back to me asking for a slicer which will dynamically help them to select the measures. I replied saying that the slicers are not intended for that purpose and should be used for filtering the data (and not for just selecting or deselecting values like Measures, which can be easily done from the Field List pane). However, the user pushed back saying that he would like to create a dashboard for the top management and didn’t want them to be using the Field List pane. That is when I had to put on my thinking cap. For the purpose of this post, I am going to use some simple mocked up data which involves three measures (Sales, Quantity and Profit) for country and year. Follow the steps below to recreate a slicer which can be used to select or deselect the measures in a pivot table:- 1) Create a table which will have the list of measure names needed in the slicer in the MeasureName column and their representative integer ids in the MsrId column. Import this table into PowerPivot using Linked Tables and name it as Msr. There is no need to make any relations with the existing tables, and Msr table should be a standalone table. 2) Now make a new measure called MsrValue with the formula given below MsrValue:=switch (TRUE,
Min ( Msr[MsrId] ) = 1,
Sum ( Fct[Sales] ),
Min ( Msr[MsrId] ) = 2,
Sum ( Fct[Quantity] ),
Min ( Msr[MsrId] ) = 3,
Sum ( Fct[Profit] )
) This measure will check the value of the MsrId column, and then use the appropriate measure. However, it should be noted that when there are more than one value of MsrId (or if more than one measures are selected), only the measure having minimum value of MsrId would be displayed. So how will we display more than one measures on the pivot table? Read on. 3) With this very simple setup, we are ready to view the results in the Pivot table. Drag and drop MeasureName column into the Slicer, Year into the Column Labels and Country in the Row Labels. Then drag and drop MsrValue in the Values and we should have something similar to the image below. I have also included the fact rows above the pivot so that we can compare. Now there are a couple of problems with this pivot table. You would have noticed that the pivot table works correctly if only one measure is selected, but if there are more than one selections for the measure name, it will only show the measure with the minimum of the MsrId (eg, Sales in the image above). Also, we would like to see the name of the measure in the column above for clarity. To solve both of these problems, follow the next step. 4) Just drag and drop the MeasureName column in the Column Labels above the Year. Now you can see that multiple measures are displayed and also that the measure names are displayed in the column above. You can also change the formatting of the measures if needed MsrValue:=switch (TRUE,
Min ( Msr[MsrId] ) = 1,
FORMAT ( Sum ( Fct[Sales] ), “$#,#0” ),
Min ( Msr[MsrId] ) = 2,
format ( Sum ( Fct[Quantity] ), “#,#0” ),
Min ( Msr[MsrId] ) = 3,
format ( Sum ( Fct[Profit] ), “$#,#0” )
) Luckily, that wasn’t as hard as I thought (which means that I could still use my evening free time to focus on more important problems like whether the chicken or egg came first into the world!). If you don’t have such important problems to focus on like me, you can download the completed file from this link and test it out on your own!

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?

Recently, I have been dealing a lot with business users and solving their problems. Even though I have been closely associated with the business in most of my previous projects, this project has given me a whole new perspective to the work I do. I am more used to the sort of work where I sit with the users, gather the requirements from them and then disappear to do my magic for at least a month. The users get to see the end result (and even the intermediate result in our scrum meetings) but they don’t get to see me working (at least not every minute of the day). In contrast, here I sit with the users from morning till evening and solve their problems along with them. We tackle the problem as a team – the users armed with their business knowledge and me with the technical knowledge. The active involvement of the users can be mainly attributed to the PowerPivot / self-service BI drive, and the company is already reaping a lot of benefits from this. It is in midst of one of these meetings that I encountered an interesting requirement – the capability to change the order of running totals.

Let me give a simplified version of the requirements below. Available Data We already have a PowerPivot file with the following tables:-

1) Product – has one column with Product names

2) CustomGroup – has one column with the CustomGroup names. Actually, this was a custom grouping of weeks like Late, Future, etc but for the purpose of this demo, we are just going to use values like G1, G2, etc. This is the field which is used for calculating the running totals, and the order in which the running totals should be calculated is specified in the Order column.

3) Stock – Will have the stock value for each Product.

4) Forecast – Will have the forecast quantity for a product and custom group.

You can download the source file (RT_Source.xlsx) from here. For the purpose of this post, the source data for these tables are sourced from linked tables in the file.

Requirement

1. Running totals of the forecast should be calculated across the CustomGroup dimension. The order in which the running totals are calculated should be easily configurable, as the order can be different for different users.
2. A report should be generated which shows the stock for each product as well as the forecast across the custom groups. However, the forecast should only be displayed if the stock would not be sufficient for it. For eg, consider the image below where the first table shows the actual data. The second table shows how the end result should look like. For P1, the forecast quantity in G1 is not displayed as it can be covered by the stock. However, from G2 onwards, all the quantities are displayed for P1. Similarly for P2, the forecast quantity for G3 is not displayed as it is less than the stock. However, for G4, only 5 is displayed as you have 125 as the forecast for G3 and G4 and you just have 120 as stock .

Implementation

The final PowerPivot report (RT.xlsx) can be downloaded from here. For implementing this from the source file, follow the steps below:-

1) I need to display Stock along with Product in the rows of the report. For that, I am going to use the product column from the Product table instead of the fact so that it filters both the stock and forecast tables. To avoid the cross join of Product[Product] and Stock[stock] columns, I am introducing Stock as a calculated column in the Product table with the formula below:-

=CALCULATE(sum(Stock[Stock]))

2) The running totals need to be done across the CustomGrp values, and hence make sure that the order column specifies the correct order in which they should be performed.

The order can be easily changed from the source sheet in excel (from where this linked table is made) in case the running total needs to be configured for other users.

3) In the forecast table, make a calculated column which will get the corresponding order for the CustomGrp for that row using the formula below

=RELATED(CustomGrp[Order])

4) Once we get the Order for that row, the running total can be made as a calculated column using the formula below

=Calculate (
Sum ( Forecast[Qty] ),
CalculateTable (
Filter (
AllExcept ( Forecast, Forecast[Product] ),
Forecast[Order] <= Max ( Forecast[Order] )
)
)
)

This formula gets evaluated for each row, and then filters out all the rows for that particular product where the Orders are less than the Order value in the current row. Then it calculates the sum of forecast quantity for the resultant filtered table, and this gets us our running total.

The running total can also be made as a measure (I have created a measure called Test in the RT.xlsx for this) but I chose to implement it as a calculated column so that the running totals are stored.

5) Now for just displaying the forecasts which are above the stock, I can create a new measure with the formula below:-

UnderstockQty:=If (
Sum ( Forecast[RT_Qty] ) <= Sum ( Stock[Stock] ),
Blank ( ),
Sum ( Forecast[Qty] )
)

Notice that I am using the running total of forecast to check against the stock and not just the forecast quantity. But this measure will not give us the final end result which is illustrated in the example below

You can see that for P2, we need to display 5 in G4 and not 75. For this, we need an additional check to see when was the first group value (or minimum sort order) when the forecast exceeded the stock.

6) Create a new measure to find the minimum Group value (or minimum order) in which the forecast exceeded the stock.

MinOrder:=Calculate (
Min ( Forecast[Order] ),
CalculateTable (
Filter (
AllExcept ( Forecast, Product[Product] ),
[RT_Qty] > Sum ( Stock[Stock] )
)
)
)

7) Now we can edit the measure to include the additional check. I am just creating a new measure called RemainingUndrStkQty so that we can compare the difference with UnderstockQty measure.

RemainingUndrStkQty:=If (
Sum ( Forecast[RT_Qty] ) <= Sum ( Stock[Stock] ),
Blank ( ),
If (
[MinOrder] = Min ( CustomGrp[Order] ),
Sum ( Forecast[RT_Qty] ) – Sum ( Stock[Stock] ),
Sum ( Forecast[Qty] )
)
)

Now you can see that we are able to get the desired results.

If you are the sort of person who observes carefully, then you would have noticed that I used a set for the group values in the columns. This was done so that if the user felt like changing the order for the running totals, then they can edit the set for changing the display order also. However, if you are using the new version of PowerPivot, a better idea might be to use the Sort by Column feature to sort the CustomGroup table by Order. Then you would be able to use the Group attribute directly in the columns and the changes to order would also be reflected immediately.

Now, to test whether your running total works if the order is modified, change the order column values in the reverse order for the groups as shown above, and you can see the result as well as the running totals change as shown below