Today I tried to solve a much occurring, simple, ad hoc business problem with PowerPivot CTP2 based on a actual reporting services report i recently build on top op SSAS and reading PivotTable Named Sets in Excel 2010. I tried to solve the following scenario: I want to see the OrderQuantity of the current month compared to the monthly average of OrderQuantity last year by promotion. This post will describe my quest for a solution and findings i have on the way. When CTP3 is released i will do the same again to see the differences.
I started by opening the gemini excel sheet I created in my previous blog post: Working with Gemini and Excel 2010 to make a pivot table. I then created a calculated field called “Cumulative Orderquantity” in the FactInternetSales table, since i was trying to get the average per month I decided to go for the following formula:
=Sum(FactInternetSales[OrderQuantity]) / Max(DimDate[MonthNumberOfYear])
I love the autocomplete feature that let’s you select datamembers as well as functions. What i didn’t get to work is to divide the Sum(FactInternetSales[OrderQuantity]) by the current DimDate[MonthNumberOfYear], i had to take an aggregation. Maybe there is a way but i didn’t find it.
Putting the new measure on the pivottable results in the following:
One major issue i find here is that the columndimensions are grouped by measure, not making it easy to compare measures. The OLAP pivottable has this by default:
You can clearly see the difference, I have created a new gemini MS connect suggestion about this issue, so if you agree with me here you can vote for my change.
Next up was creating a way to select the current month and last month of last year. I know that in CTP3 time intelligence functions will be available so i will be using them when CTP3 is released. My first idea was to use the named sets as used here PivotTable Named Sets in Excel 2010 and select the months I want myself.
So i started to create the set by simply going to PivotTable Tools -> Options in the ribbon, and selecting “Create set based on column items”. Now the Named Set creation UI will pop up:
We can easily remove the tuples I no longer want, in my case i want to select the current month with the sum OrderQuantity and month 12 of last year to show the “Cumulative Orderquantity”. After creating the set I tried to select the set in the PivotTable, this appeared to be currently not available in Gemini CTP2, luckily Donald Farmer told me on twitter named sets will be supported in CTP3 so to be continued. What i’m wondering is how the CTP3 time intelligence functions can be selected in the sets but i guess we see soon enough with CTP3 next month.
Since the set can’t be select I tried to select the two months by using a filter, but a relation between month and year is not made in the filter, you can only select a month and not a month in a year, meaning you can only select a month that will be selected for all the years.
Not very pratical, i created a connect suggestion mentioning this problem.
These are my findings so far, I couldn’t solve my business case with CTP2 of PowerPivot. I have good hopes CTP3 will get me a lot further and maybe even solve it. I hope a lot of work is spent on user friendliness since Excel is the GUI the end user must be able to create the comparisons.