MDX Help.

This is my current MDX for a KPI in Performance Point:

(([Current Month].item(0), [Measures].[Revenue]) - ([Current Month].item(0).PrevMember, [Measures].[Revenue]))/ ([Current Month].item(0).PrevMember, [Measures].[Revenue])

I need to modify this to only include Customers of a of Type Dealer and Distributor in my cube.

[Customers].[Customers].[Customer Type].&[Dealer]

[Customers].[Customers].[Customer Type].&[Distributor]

[Customers].[Customers].[Customer Type].&[Direct] (do not include)

Wow...it seems like it should be so easy...but I cannot get the syntax.

Help!!!!

Linda

Hi Linda,

You could create a calculated member to agrgegate the 2 included customer types, like:

Create Member CurrentCube.[Customers].[Customers].[Dealer-Distributor] as

Aggregate({[Customers].[Customers].[Customer Type].&[Dealer],

[Customers].[Customers].[Customer Type].&[Distributor]} )

and another to compute Revenue Growth %, like:

Create Member CurrentCube.[Measures].[RevenueGrowth%] as

([Measures].[Revenue] - ([Date].[Month].PrevMember, [Measures].[Revenue]))

/ ([Date].[Month].PrevMember, [Measures].[Revenue])

Then the KPI could be:

([Current Month].item(0), [Customers].[Customers].[Dealer-Distributor], [Measures].[RevenueGrowth%])