turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Web Report Studio
- /
- Need help with a calculated measure

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-20-2011 07:42 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-15-2011 06:41 AM

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

Read more here: http://support.sas.com/documentation/cdl/en/mdxag/59575/HTML/default/viewer.htm#a002253381.htm

Hope this helps!

Tricia A

Blog: BI for SAS BI Users

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-06-2011 10:27 AM

Hey Ulf:

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

Percent of Total MDX Calculation

Hope this helps!

Tricia

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-07-2011 09:48 AM

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*])