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.
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.
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.
3) Make inactive relationships from the fact table to the Year, Month, Type and Category tables.
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.
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.
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.
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
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
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!
