topic Need help with a calculated measure in SAS Web Report Studio

Need help with a calculated measure

UlfB — Thu, 20 Oct 2011 11:42:17 GMT

Hi all,

I’ve been trying to create a calculated measure in Cube Studio for calculating a ratio, but I’m stuck and could use some assistance.

My problem is the following:
As an example let’s say we have a cube with three dimensions, Time, Geography and Gender, and one measure, Quantity. The Time dimension only has one level, month, in the format YYYY-MM. What I would like to have is a new measure that calculates the ratio per month.
If I create a report in WRS based on the cube and set a group break on the Time dimension, it would look something like this if I for instance select month 2011-09 (where the total for Quantity is 12):

Geography	Gender	Quantity	Ratio
Africa	Female	2	16,7%
Africa	Male	2	16,7%
Asia	Female	2	16,7%
Asia	Male	1	8,2%
Europe	Female	2	16,7%
Europe	Male	3	25%

Somehow I want to use the grand total of Qantity for whatever month I’m currently looking at as denominator in the calculation so that the formula will be 'Value in current cell' / 'Grand total for current month', but I can’t figure out how to accomplish that.

Any help is appreciated!

Thanks in advance,

Ulf

Need help with a calculated measure

TriciaA — Tue, 15 Nov 2011 11:41:00 GMT

I suspect you are going to have to use the MDX code (if you are not already).

Hope this helps!

Tricia A

Blog: BI for SAS BI Users

Need help with a calculated measure

TriciaA — Tue, 06 Dec 2011 15:27:08 GMT

Hey Ulf:

One of the bloggers on my site published this information recently:

Percent of Total MDX Calculation

Hope this helps!

Tricia

SAS BI Notes Blog

Need help with a calculated measure

UlfB — Wed, 07 Dec 2011 14:48:42 GMT

Thanks for your replies! Actually we might have solved it already, I just forgot to update this post :smileyblush:

A colleague of mine had some spare time so he sat down with this problem, and after some trial and error he came up with a working solution. When he specified the measure over all dimensions but the time dimension (if that makes any sense, I don’t know how to express it) as denominator, the result was the total Quantity for any given month regardless of what drilldowns had been made in the other dimensions.
So for my example above the MDX for calculating the ratio would look like this (it probably says more than my attempt at an explanation):
[MEASURES].[Quantity] / ([MEASURES].[Quantity],[Geography].[All Geography],[Gender].[All Gender])

In short, just line up all dimensions in the cube apart from the time dimension in the denominator:
[MEASURES].[measure] / ([MEASURES].[measure],[Dimension-1].[All Dimension-1],[Dimension-2].[All Dimension-2],[Dimension-3].[All Dimension-3], … ,[Dimension-n].[All Dimension-n])