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.
thanks!….keep um coming
Hi Kasper, interesting post. A few comments:
“since i was trying to get the average per month I decided to go for the following formula: =Sum(FactInternetSales[OrderQuantity]) / Max(DimDate[MonthNumberOfYear])”
A formula such as =CALCULATE(SUM(FactInternetSales[OrderQuantity])/COUNTROWS(DISTINCT(DimDate[EnglishMonthName])),ALL(DimDate[EnglishMonthName]))
should provide the correct values for month average. Oddly, when I tried the formula with the Adventure WorksDW2008 database, it computed a value for August 2004, which has no data. So for this year only, the month average was computed over 8 months instead of 7 months.
“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:”
That’s odd. When I created the calculated measures, they appeared next to the standard aggregate for the year (like you show in the OLAP figure). What’s not clear is why the year field appears at the bottom of the column hierarchy in your case. I didn’t have this problem.
“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.”
I’m looking forward to those time intelligence functions. One possible way to do the selections you want is to create slicers for month and year. Since either or both of these fields could end up in the pivot table, to use the fields also as slicers requires duplicating the fields in the Gemini DimDate table i.e. creating calculated columns for month and year.
“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.”
I couldn’t agree more. While the issue of time intelligence might be solved, there are other issues that would challenge even seasoned Excel users.