Calculate the ratio of two row totals

Questions & Answers
1

Hi,
I would like to calculate the ratio between the “Hours spent” and the “Calculated member column” (which, as the name suggests, is a calculated column).

In the example I would like 0.064 to return instead of $total_aggregate.

As you can see, I reach the cell, but I don’t know how to take the two totals to do the operation.

Is there a way?

image
image883×265 32.8 KB

Thank you

2

Hi @nicolap ,

To get the results for each row, including the Total row, you should write the expression using dividing. And add logical function CASE WHEN to avoid dividing by 0:

CASE WHEN 
  --avoid dividing by zero
  [Measures].[Calclulated member colum] <> 0
THEN
  [Measures].[Hours spent]
  / 
  [Measures].[Calclulated member colum]
END

If you would like to represent data only for the Total row, add one more condition to CASE WHEN. This condition checks if the rows name is “$total_aggregate” (looks a bit complex, but this is universal expression):

CASE WHEN 
  --only for the Total row
  Dimensions(
    --find the first dimension name on report rows
    VisibleRowsSet().Item(0).item(0).Dimension.Name
  ).CurrentHierarchyMember.Name = "$total_aggregate" AND
  --avoid dividing by zero
  [Measures].[Calclulated member colum] <> 0
THEN
  [Measures].[Hours spent]
  / 
  [Measures].[Calclulated member colum]
END

More details on the mentioned functions are described in the documentation: MDX function reference.

Best,
Zane / support@eazyBI.com

1 Like