BookmarkSubscribeRSS Feed
Calcite | Level 5

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):


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,


Calcite | Level 5

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

Read more here:

Hope this helps!

Tricia A

Blog: BI for SAS BI Users

Calcite | Level 5

Hey Ulf:

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

Percent of Total MDX Calculation

Hope this helps!


SAS BI Notes Blog

Calcite | Level 5

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. 

Register now!

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.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2 in conversation