MDX: Excluding an attribute leaf from a calculation

Recently I received a request to do an interesting MDX calculation inside of the SSAS cube. Essentially, what they wanted to see was a measure that showed the grand total, but excluded a certain leaf from the calculation, as they were the unknown values. For the business scenario, in this case unknown didn’t mean bad data, but rather data that didn’t apply at that level but applied at a higher grain in the fact table. Modeling aside, it was the best approach available. As an example of what we were trying to accomplish, take the following Excel pivot table connected to the Adventure Works cube, looking at customers and sales:

 

image

 

And what they wanted to see was the grand total of all customers in another measure next to it, let’s called it “Total Internet Sales”. This is really useful if you want to calculate the percent of a measure to the total sales. Now let’s refine the requirement based on this scenario. Let’s say that Aaron A. Allen is an internal employee, and for whatever reason we don’t want to include him in the total sales calculation. To start, let’s create a measure that shows the total internet sales for all customers. The easiest and most efficient MDX statement we can use is:

 

(Root([Customer]),[Measures].[Internet Sales Amount])

Which tells MDX return the tuple that corresponds to the ALL member of, in this case, the Customer dimension. Putting this in the calculated member returns the following in Excel:

 

image

Now on to the original requirement. Remember that we want to calculate the total for every customer except for Aaron A Allen. Unfortunately, in this case the root function won’t work, what we need to do is to modify this to exclude only the member attribute we don’t want to see. Let’s see the finished MDX, then we’ll break it down. The MDX is:

 

AGGREGATE
(
EXCEPT
(
[Customer].[Customer].[All Customers].Children,
[Customer].[Customer].&[20075]
)
,[Measures].[Internet Sales Amount]
)

Which gives us a measure that appears as:

image

 

And we can verify this is correct, by taking 62,653,307.52 – 62,649,908.21 = 3,399.31, which are Aaron A. Allen’s sales. So let’s go back to that formula. What are we doing? Let’s start with the inner most function, EXCEPT. EXCEPT takes two sets, and returns the difference between the set, excluding duplicates (we can specify them to retain, if we want). The first argument is what we want to keep, the second argument is what we want to exclude. So in this case, we want to include All Customers, and their children (in the MDX sense, pun intended), and we want to exclude customer 20075, which is Aaron A Allen.

 

The second piece of our formula is the AGGREGATE function, which takes a set expression as argument 1, and a measure as a second argument. So the first part of our aggregate function (our set), is the EXCEPT clause of our query:

EXCEPT
(
[Customer].[Customer].[All Customers].Children,
[Customer].[Customer].&[20075]
)

The second argument, the measure, is ,[Measures].[Internet Sales Amount]

 

Yay! It works, and we understand it! Now let’s add to our fun little adventure here. Let’s say that customer Xavier Turner is also an employee, and we want to exclude him from our total sales amount too. If your familiar with sets in MDX, you might be tempted to do this:

 

AGGREGATE
(
EXCEPT
(
[Customer].[Customer].[All Customers].Children,
{[Customer].[Customer].&[20075]:[Customer].[Customer].&[14343]}
)
,[Measures].[Internet Sales Amount]
)

 

But building and deploying this returns this value, which is wrong:

 

image

 

Why? Well, what we’ve done is to tell it to exclude not just Aaron A Allen and Xavier Turner, but every customer in between in that entire range. What we need to do is to nest the except clause to exclude only Aaron A Allen and Xavier Turner, so our query instead should appear as:

 

AGGREGATE
(
EXCEPT(
EXCEPT
(
[Customer].[Customer].[All Customers].Children,
[Customer].[Customer].&[20075]
)
,[Customer].[Customer].&[14343])
,[Measures].[Internet Sales Amount]
)

 

This tells MDX we only want to exclude Aaron & Xavier, but not everyone in the range in between. Our result after this should appear as:

image

 

In conclusion, in MDX if you want to exclude certain attributes from your calculation, make good use of the EXCEPT function.