Total Sales - Same Stores

[rse...@gmail.com]

I am trying to write an MDX calculated measure that displays store
sales ONLY for stores that were open last year. I also need the ALL
member to be a sum of those stores. Here is what I have so far:

with member [Measures].[Total Sales - Same Store] as
iif([Location Dimension].[Name].CurrentMember is [Location Dimension].
[Name].[All],
Sum(
Filter(
[Location Dimension].[Name].Children,
([Measures].[Total Sales],[Time Dimension].[Fiscal
Year].PrevMember) > 0)
, [Measures].[Total Sales]
),
Sum(
Filter(
[Location Dimension].[Name].CurrentMember,
([Measures].[Total Sales],[Time Dimension].[Fiscal
Year].PrevMember) > 0), [Measures].[Total Sales]
)
), format_string = "Currency"

This seem to work correctly, but I just want to see if anyone has a
more straight forward approach?

[yongli]

Hi,

You could define a set for your Filter() part first, then use the defined
set in your measure expression.

with set [] as 'Filter()'
member [] as '...'

[Deepak Puri]

Assuming that you're using AS 2005:

>>
with member [Measures].[Total Sales - Same Store] as

Sum(
Filter(
existing [Location Dimension].[Name].[Name],

([Measures].[Total Sales],
[Time Dimension].[Fiscal Year].PrevMember) > 0)
, [Measures].[Total Sales]

), format_string = "Currency"
>>

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***