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

Showing results for

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 10-20-2011 07:42 AM
(795 views)

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

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.