PowerPivot

Measure Selection using Slicers in PowerPivot

Measure Selection using Slicers in 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. 1 Measure selection using slicers in powerpivot 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. 2 Data model and sample data 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. 3 Create Linked table in powerpivot 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] )
) 4 New measure 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. 5 Drag column to pivot table and insert slicer 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. 7 Field list pane Now you can see that multiple measures are displayed and also that the measure names are displayed in the column above. 6 Measure getting selected in pivot table as per the slicer selection 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” )
) 8 formatted measures 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!

Posted by SQLJason, 31 comments
Difference from Maximum of Running Totals in PowerPivot

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?

Posted by SQLJason, 4 comments
Configurable Running Totals in PowerPivot

Configurable Running Totals in PowerPivot

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.

Configurable running totals in PowerPivot

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.

Source tables

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 .

Actual data and final report

 

 

 

 

 

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

Adding stock as a calculated column

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.

verifying the order column in CustomGrp

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

Getting the corresponding order column for the row

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

calculate running total

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.

running total of forecast quantity

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

Understocked quantity

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

min order

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.

end result

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.

sort by column

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

results when order is changed running total when order is changed

Posted by SQLJason, 3 comments
Linking and Brushing Visualization with PowerPivot

Linking and Brushing Visualization with PowerPivot

The other day, I was reading a great post by David Churchward (twitter) on Gantt Chart with Resource Loading. PowerPivot is really powerful with manipulating the data and everyone knows that, but his post made me think from a visualization perspective. Maybe PowerPivot deserves more credit than it usually receives in this department and I decided to test the brushing technique that I described in my last post with PowerPivot. I must say that I was pleasantly surprised with how easily you can achieve it in PowerPivot.

Picture1

With all the buzz around hurricane Isaac and also considering that I look into the weather a lot nowadays (because I drive 160 miles daily for work), I downloaded a list of the main tropical storms and hurricanes in the US for the last 5 years. You can download the completed PowerPivot v2 workbook from here, if you are interested to go through the data or the technique. To reproduce the visualization, follow the steps below

1) Import the storm data into PowerPivot using the Create Linked Table option. You can download the data from here.

1 Create Linked table for fact

2) Once that is done, we will have to make dummy tables in PowerPivot for each slicer that we need to analyze by. In my case, I am going to analyze by the Year, Month, Storm Category and Storm Type. So we will need to make new sheets for each of these 4 columns, remove the duplicates and then import them into PowerPivot using the Create Linked Table option.

2 Create linked tables for slicers

3) Make inactive relationships from the fact table to the Year, Month, Type and Category tables.

3 Mark inactive relations

4) Next, we will be creating a measure to have the count of all storms in the Fact table.

WindCnt:=CountRows (
Values ( Fact[Name] )
)

5) Now, create 2 measures – one which will show the count of storms for selected values and one for the rest of the count.

SlicedCnt:=Calculate (
[WindCnt],
USERELATIONSHIP ( Fact[Year], Year[Year] ),
USERELATIONSHIP ( Fact[Type], ‘Type'[Type] ),
USERELATIONSHIP ( Fact[Cat], Category[Cat] ),
USERELATIONSHIP ( Fact[Month], Month[Month Name] )
)

UnslicedCnt:=[WindCnt] – [SlicedCnt]

Note that the [WindCnt] and [UnslicedCnt] measures will not be impacted by the change in the 4 new tables that we created, since the relationships are inactive. However, the [SlicedCnt] measure will change based on the 4 new tables as we are using the USERELATIONSHIP function to relate.

4 Measures

6) Now we can create a PivotChart from the PowerPivot tab which has the SlicedCnt and UnslicedCnt on the Values, Fact[Month] on the categories and slicers for Month[Month No], Year[Year], Type[Type] and Category[Cat]. Basically, the sources for the slicers are the 4 new tables we created while the chart will have the field from the fact table. Make sure that the chart type is Stacked Column Chart.

5 Pivot chart

You can safely ignore the relationship warning shown in the PowerPivot field list. 7) Similarly, create 2 other charts with Fact[Year] and Fact[Cat] on the category respectively. Also ensure that the slicers are connected to all three pivot tables. This can be done by right clicking on each slicer, selecting the PivotTable Connections property and then selecting the checkbox for all three pivot tables as shown in the image below.

6 Pivot table connections

8) Now you can click on the slicers and see the chart get the brushing effect for the selection. For eg, if I select Hurricanes of Cat 4, I get the following result

7 preview

We can easily see the relationship that there is a hurricane of cat 4 in all the years after 2008, and that it usually strikes in Aug or Sep. We can also get a rough idea of the total numbers with respect to the selected values. If all the values in the slicers are selected, you will get the following result

8 brushig effect initial view

Hopefully, this post will be helpful to you guys for visually unleashing the power of PowerPivot!

Update : 31/8/2012 Javier Guillen (blog | twitter) has come up with an easier idea for doing the calculations. We can keep the relationships as active and instead use these calculations for the measures

WindCnt:=Calculate (
CountRows ( Values ( Fact[Name] ) ),
AllExcept ( Fact, Fact[Cat], Fact[Year], Fact[Month] )
)

SlicedCnt:=CountRows (
Values ( Fact[Name] )
)

UnslicedCnt:=[WindCnt] – [SlicedCnt]

I think this is a better idea as we don’t have to make dummy tables just for the sake of slicers and can reuse the dimension tables if it is already there. Thanks Javier!

Posted by SQLJason, 0 comments
Pivot Training

Pivot Training

The back pivot is used to create space. Why is this important to understand? The goal of the offense is to score. The easiest way to create an opportunity to score is to create space. The opposite is true for the defense. The defense’s goal is to prevent the offense from scoring. The defense’s best method of preventing this possibility is to take away the offense’s space.

This yin and yang are a key to understanding individual offensive and defensive decision making. An offensive player who understands how to create space will create, and convert a higher percentage of scoring opportunities. Improve your training results by reading this resurge review.

The back pivot individual offensive scoring move can help an offensive player create space. This is particular important vs. good defensive teams because good defensive teams give up fewer uncontested lay-ups at the rim. In fact, I would go so far as to argue that a good defense rarely gives up a one foot lay-up in the half-court.

The rise of the back pivots importance to players of all levels is proportional to the improvement of team and individual defense. It is hard to score against good defense, but the back pivot can give an advantage to the offense at the rim.

Another important reason to teach the back pivot, especially to youth basketball players, is that it is a direct application of basketball footwork. It is a more “exciting” way to work on footwork. Important aspects of footwork are applied in the back pivot. These include running and stopping, dribbling and stopping, balancing, staying low in a ready position, maintaining a wide base, incorporating front and reverse pivots, movements into space, and creating a shoulder to chest advantage.

What is the Back Pivot

There are other names for this move. We have called it a “Nash” in the past after Steve Nash, who I first learned it from. The back pivot is by definition a front pivot on the back foot. A front pivot is when a player leads with their nose, instead of their butt like would happen with a back pivot. The move is applied from a shoulder to chest position with a defender or the basket. This means that the offensive player stops with their shoulder facing the rim rather than being square, or chest to chest with the basket. Visit discovermagazine.com for more healthy information about resurge.

Back Pivot

Uses of the Back Pivot

The back pivot can be used to create space for a shot or a pass. It is most often applied on a dribble drive. It can also be used off of a catch or an offensive rebound. A cutter on an off the ball cut, or on a pick and roll can use a back pivot without a dribble to shoot. Similarly, without a dribble a player who gets an offensive rebound can rebound the ball and back pivot to shoot or pass. Learn more about exercises and supplements at https://www.metrotimes.com/detroit/resurge-reviews-expose-new-updated-packages-and-hidden-information/Content?oid=24851297.

When to Use a Back Pivot

The back pivot is best applied within the scoring area near the rim. You can define this however you want, but we generally say it is with at least one foot in the key. It can be used when the defender covering the ball cuts off the path to the basket. It can also be used the a help side defender rotates to cut off the offensive player’s path to the basket.

What is the Decision Cue to Apply the Back Pivot

Reading advantage and disadvantage comes back to understanding whether an offensive player has space or not. As discussed in a previous blog one of the decision cues for a player is to read whether they have a shoulder to chest or chest to chest relationship to the defender.

  • If an offensive player who is dribbling gets cut off early by a defender, they apply a dribble counter.
  • If an offensive player gets cut off late by a defender, either their check or the help side defender, they apply a back pivot.

Back Pivot Counters

A counter is a secondary move applied to an offensive move to counter a defensive coverage. Improve your results after reading these Carbofix reviews.

  • If the defender jams the back pivot, the offensive player can apply an up and under or reverse pivot counter.
  • If the defender recovers to take away the up and under move, the offensive player can apply a third pivot. A third pivot is a continuation of the up and under with another front pivot to a shot.
  • If the defender recovers to take away the reverse pivot counter, the player can use a front pivot to come back to the shot.
  • Lastly an offensive player can use a fake double drop to counter a defender who anticipates the back pivot move.
Posted by SQLJason, 4 comments
PowerPivot tutorial videos

PowerPivot tutorial videos

Waking up early morning for a technical webinar is so not me (I really love to have my sleep in the morning!); but then I was very interested in the PowerPivot webinar which was going to be taken by Rupesh Patric (Microsoft Partner Technical Consultant and an accomplished artist) exclusively for people from my company, MindTree Ltd. As I was the only one from UK, the time was adjusted for the majority people in India which meant I had to wake up early. Ofcourse, I knew I wouldn’t regret losing my beauty sleep from the very moment the presentation started and overall, it was a good introduction to PowerPivot. To top it all, Rupesh sent a goodie pack containing some video tutorials for PowerPivot and I thought it would be too selfish of me if I didn’t share it with you guys. powerpivot-logo Materials 1.     Deck : SQL Server 2008 R2 Overview 2.    Book : Introducing SQL Server 2008 R2 3.     Hands On Lab – PowerPivot in SQL Server 2008 R2.zip Set 1 1.     Presentation What’s New in the Excel 2010 Client 2.     Demo What’s New in the Excel 2010 Client 3.     Presentation Loading and Preparing Data for Analysis in SQL Server PowerPivot for Excel 2010 4.     Demo Loading and Preparing Data for Analysis in SQL Server PowerPivot for Excel 2010 5.     Presentation Creating Reports with SQL Server PowerPivot for Excel 2010 6.     Demo Creating Reports with SQL Server PowerPivot for Excel 2010 7.     Presentation Defining DAX Calculations with PowerPivot for Excel 2010 8.     Demo Defining DAX Calculations with SQL Server PowerPivot for Excel 2010 Set 2 1.     Part 0 Installing the PowerPivot Add-In for Excel 2.     Part 1 Using the PowerPivot Add-in to Import Data into Excel 2010 3.     Part 2 Import data from a SQL Server Database 4.     Part 3 Import Data from an Access Database 5.     Part 4 Using Copy-Paste to Import Excel data 6.     Part 5 Using a Linked Excel table to Import data 7.     Part 6 Compressed data in PowerPivot for Excel 8.     Part 7 Handling big data in PowerPivot for Excel 9.     Part 8 Importing SAP data in PowerPivot for Excel 10.  Part 9 Reports as data sources in PowerPivot for Excel 11.  Relationships – 1 Overview 12.  Relationships – 2 Creating Relationships If you are interested in Business Intelligence video tutorials, I would recommend you to visit Microsoft’s Channel 9, plenty more of the good stuff there. Update (18/03/2011) Another great Microsoft Technet resource – PowerPivot for Excel (Business Intelligence) was brought to my notice by Michele Hart (Microsoft). This site serves as a one-stop place for your PowerPivot requirements, be it articles, whitepapers, blogs, samples, videos or information about PowerPivot books. But the best part is the Virtual Lab where you can actually work and get your hands dirty on a system. Check it out!

Posted by SQLJason, 1 comment