Home > Sql Server Analysis Services > Handling Multiselect Filter problem in Excel with Calculated Measures AVG,MAX etc.

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


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 🙂

 

 

Advertisements
  1. December 18, 2011 at 7:42 PM

    You can find some information about Dynamic Named Sets in Mosha’s article.
    http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx

    But i still cant find any solution for SSAS2005 😦

  2. sv
    December 20, 2011 at 3:34 AM

    Interesting article. But, your example is for one dimension and one measure. Is there a more generic use of dynamic sets to control multi selectfilter in Excel for many dimensions and measures. For example, I modify, with limited success, the calculate function with dynamic sets…

    Calculate;
    this = iif(
    drilldownlevel([Region Set],
    [Region].[Region].[Region – Detail]).count = 1
    or
    drilldownlevel([Region Set],
    [Region].[Region].[Region – Detail]).count =
    drilldownlevel([Region].[Region].[Region – Detail],
    [Region].[Region].[Region – Detail]).count,
    [Region].[Region].currentmember.datamember,
    null)

    I created dynamic set (SSAS 2008) and further modify calculate statement to detect multi item selection and set calculation to NULL in that situation (since not able to calculate right figures).

    Also, FYI, my cube is based on pre-aggregate data (see currentmember.datamember)

    Using this methodolody.
    http://www.ssas-info.com/analysis-services-articles/61-performance-tuning/368-loading-aggregate-data-in-yukon-2005-by-richard-tkachuk

    So, is there a way to make Excel multi item selection works in presence of pre-aggregated data and/or in general (with a generic approach handling multiples dimensions and measures at the same time)?

    Thanks!

  3. May 29, 2012 at 8:54 AM

    Sorry for late response. But it is a big problem for Excel formulas nobody has found any solution 😦
    In SSAS Enterprise it is easy by setting AggregateFunction to MAX. But in SSAS Standard Edition i could not find any solution for that.

  4. October 31, 2013 at 2:00 PM

    Tableau Software
    I work on our Professional Services team.We have Business Consultants that can help in several ways.Our company which is located in London, Here are some of services that we offerhttp://www.sqiar.com/solutions/technology/tableau/. Click on the link of that page to send us an email. We’d be happy to help. Professional Services Tableau Software

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: