Posts Tagged ‘excel’

Handling Multiselect Filter problem in Excel with Calculated Measures AVG,MAX etc.

December 18, 2011 4 comments

In my customers most of the users are using Excel2007/2010 for accessing OLAP data.
Maybe someone has already blogged about it but  i want to share my experience.
I will use Adventure Works 2008 Cube in my example.

The article is about MultiSelect Filter problem in Excel.

Lets take  [Internet Sales Amount] as a measure and look at the values in Date.Calender hierarchy.













Now i will write a formula to calculate the AVERAGE Sales Amount.

CREATE MEMBERCURRENTCUBE.[Measures].[Average Internet Sales Amount 1] AS

Avg( Descendants([Date].[Calendar].CurrentMember,[Date].[Calendar].[Month],SELF),[Measures].[Internet Sales Amount])

Now lets test the value i am choosing January, February and March 2008 as Month filter. The value should be $1,427,876.65 as shown above.











ok we saw that [Averagete Internet Sales Amoun 1] is true.

Now lets select January 2008 and February 2008. And look at the Average before filtering it.














The Average is $1,401,362.39

Lets Drag the hierarchy to the filter any select January 2008, February 2008.










 Opps the value is $793,477.88 and it is not true.

We can write a mdx query to see the correct value, but as i sad before the aim to see the right value in Excel.

ok what to do to see the correct value.  We are writing the same formula, but this time i am using SET in my formula.







Lets check the query





We see $1,401,362.39 as [Average Internet Sales Amount 2]. This is the value we expected to see.

Happy New Year 🙂