PowerPivot

Measure Selection using Slicers in PowerPivot

Measure Selection using Slicers in PowerPivot

November 21, 2012

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, 25 comments
Difference from Maximum of Running Totals in PowerPivot

Difference from Maximum of Running Totals in PowerPivot

October 18, 2012
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

October 1, 2012

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

August 30, 2012

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
PowerPivot Training

PowerPivot Training

May 25, 2012

A quick word to all those guys in the USA who are looking to get trained in PowerPivot. PowerPivot is the buzz word in the industry right now and companies are drawn to it like bees to nectar because of it’s in-memory and self-service BI capabilities. My company, Mariner in partnership with SQLBI are offering PowerPivot workshops and you can find more details in the flyer below. Feel free to reach out to me in case you have any questions.

Forward to a friend | Visit our website | View as  a webpage  
Mariner.jpg
PowerPivot Training
PowerPivot Training Comes to NC!
 
    Public Course Now Available
 
padBe a PowerPivot Power User
In 2 days, learn the basics concepts and progress to complex data models and DAX formulas. At the end of the training, you will know how to use PowerPivot in the real world.  Download the workshop outline.

Ideal for Business & Data Analysts, IT Pros
It is designed for people like you eager to use PowerPivot’s full capabilities. You’ll learn the concepts and techniques needed to implement PowerPivot and make critical business decisions using the most popular BI tool on the planet, Excel.

Learn from Leaders
In partnership with SQLBI, Mariner offers PowerPivot workshops in the southeastern US.  Offered on-site at your location or at these new public venues.  Mariner is the exclusive provider of the PowerPivot Workshop in the southeastern US

Register by June 13, 2012 to guarantee your seat.

Mariner is 1 of just 2 companies in the US with access to the training materials developed by PowerPivot gurus and authors, Alberto Ferrari and Mark Russo.  This exclusive partnership enables us to run these workshops for you.

 
    Details
Price
$1,300 per student
includes workshop and training manual

Date & Time
June 18 & 19, 20128:30am-5:00pm
(with an hour for lunch)Location Aloft Hotel-Ballantyne 13139 Ballantyne Corp Pl
Charlotte, NC
Get directions

Prefer another city?
 Let us know!

 
    Your Trainer, Javier Guillén
 
Javier is a Business Intelligence Specialist (MCTS / MCITP SQL Server 2008 BI) at Mariner, dedicated to helping people maximize SSAS & PowerPivot (including MDX and DAX). A Microsoft Community Contributor, you may have seen him speak at PASS events and most recently at Carolina Code Camp.  Check out his blog, connect with him on LinkedIn or follow him on Twitter.
LinkedinTwitter
 
 
    About Us
For more than a decade, Mariner has been helping clients leverage their data – from any data source to any device – through the use of business intelligence, analytics, data warehousing, business scorecards and performance management dashboards. Mariner serves clients in a variety of industries including utility, healthcare, education, media and manufacturing sectors. Singularly focused on providing analytic solutions, we help business people organize, use, analyze and share data to improve sales, customer service, logistics, productivity and financial insight. Mariner is a Microsoft Certified Gold Partner with competencies in Business Intelligence and Data Platform. Mariner-Insight to Achieve. www.mariner-usa.com
 
Copyright  2012 Mariner All Rights Reserved.
2719 Coltsgate Road | Charlotte | NC 28211 |info@mariner-usa.com

Facebook Twitter Linkedin YouTube

Posted by SQLJason, 4 comments
PowerPivot tutorial videos

PowerPivot tutorial videos

February 12, 2011

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