CODEWITHSUNDEEP
ssasmdx
Kevin
Kevin

Reputation: 13

Create MDX to Divide two measures for each month and then sum the all of the months

I have a multidimensional cube that needs a custom measure that I'm not sure how to build.

That data looks like this: 

MemberID-----Date-------EventType
1--------------1/1/2016-------1
2--------------1/1/2016-------2
3--------------2/1/2016-------1
2--------------2/1/2016-------2
4--------------2/1/2016-------2

There is a count measure in the cube, but others can be added if needed. I need to create a measure that will use whatever filters the user applies and then count the EventType (1 and 2 only) by month, divide the resulting counts for EventType 1 into the count for EventType 2 (for each month individually), and finally sum the monthly results. For example 1/1/2016 would be 1/1=1 (count of EventType 1 and count of EventType 2) and 2/1/2016 would be 1/2=0.5 so the resulting measure value for the two months would be 1+0.5=1.5. Any help is greatly appreciated.

Upvotes: 0

Views: 1668

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

Let's assume you have a Date dimension with an attribute called Month. And let's assume you have an EventType dimension. And let's assume you have a count measure in your measure group called Cnt. Here's what else you need to do.

First, go to the DSV and add a new calculated column to the fact table which is called NullInt and is the following expression: cast(null as int)

Then create a new Sum measure in your measure group off that column and call the measure My Rollup. Under the Source property, change NullHandling to Preserve so that it will start off null.

To explain why we're doing this, a scoped assignment to a physical measure will aggregate up. (If you assign a value to a physical measure at the grain of each month, then it will rollup to the grand total.) But a scoped assignment to a calculated measure doesn't roll up.

Then in your MDX script add the following calculations:

scope([Date].[Month].[Month].Members); //calculate at the month level then rollup
  [Measures].[My Rollup] = DIVIDE(
   ([Event Type].[Event Type].&[1],[Measures].[Cnt]),
   ([Event Type].[Event Type].&[2],[Measures].[Cnt])
   );
end scope;

Note that your version of SSAS probably has the DIVIDE function if it's AS2012 with the latest service pack or newer. But if it doesn't, you can always do division the old fashioned way as IIF(denom=0,null,num/denom).

Upvotes: 1

Related Questions