Trying to solve a business problem with Powerpivot, Gemini CTP2
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: