Home > SSAS > SSAS #27 – How filtered (calculated) measures are aggregated along dimensions?

SSAS #27 – How filtered (calculated) measures are aggregated along dimensions?

In an SSAS cube I designed recently, I created a dimension (a junk dimension) where it has either Approved or Declined value for an attribute hierarchy called “Decision”. Most of the queries will just focus on the approved count and amount, without showing the declined amount. This naturally lead me to create two calculated measures, Count Approved, and Amount Approved.

Here is the simple MDX script to filter the count for Approved only.

CREATE MEMBER CURRENTCUBE.[MEASURES].[The Count Approved]
AS ([Measures].[The Count], [The Decision].[Decision].&[Approved]),
FORMAT_STRING = "#,#",
VISIBLE = 1;

image

 

To make sure that the calculated/filtered measure behaves as I expected, I did this simple browsing (in BIDS or in SSMS).

1. How the calculated/filtered measure is aggregated along dimensions other than The Decision dimension.

It’s good to see that the measure is summed over the Time dimension as expected. Sum is the default Aggregation Function for the original measure.

image

2. How the calculated/filtered measure is aggregated along The Decision dimension.

It’s also good to see that the measure is NOT aggregated in any way along the Decision hierarchy. This is also expected. Notice that the Approved measure also stays the same for the different members of the attribute hierarchy. This does look a little odd at first, but this should be also expected.

image

Categories: SSAS
  1. The Hurricane
    October 29, 2012 at 2:50 pm

    can you explain #2 more… why is this result desired?
    thanks

    Like

  2. The Hurricane Dev
    October 30, 2012 at 1:45 pm

    Hi, can you explain why the measure is not aggregated over the junk dimension?

    Like

  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: