3 Replies Latest reply on Nov 23, 2016 1:56 AM by Gavin Attard

# calculations with different cells.

Hi Guys

what i am probably asking for can't be done, however i thought i'd put out there to see if anyone has managed.

So i have a report that summs metrics against a couple of dimensions.

see attached pic.

What i want to achieve is the ability to multiply the values in the cells marked in yellow.

Any ideas? • ###### 1. Re: calculations with different cells.

This will be possible although looks like it could be complicated as you have a table calculation involved.

I can give you a guide on how to do. You need to extract the values in yellow within calculated fields, for example for the CG% it could be:

IF [FieldName] = 'CG' THEN [% Value] END

In reality I expect you will have to go deeper than that, back to the source of the % formula but using the same technique. This will return NULL for non CG values.

The same is true for the NCG total - IF [FieldName] = 'NCG' THEN [TotalField] END.

With the NCG total I also expect you will have to go deeper to pull out the NCG numbers.

Once you have the required CG and NCG numbers you're able to create a new field to multiply these.

For example:

IF [FieldName] = 'NCG' THEN [TotalField] END * IF [FieldName] = 'CG' THEN [% Value] END

• ###### 2. Re: calculations with different cells.

Hello Gavin,

i've just made an attempt but the one which your trying to implement is not possible i guess or might be done using LOD expressions. But just created a workbook what you requested for

Regards,

Sudhakar Reddy

• ###### 3. Re: calculations with different cells.

Hi Andrew

thanks for your help. Indeed that was the route i went down, and was able to pull the individual values. However the challenge i had is when i tried to multiply them. The issue was as you pointed it keeps hte metrics agaisnt the dimension, and is not able to multiply the cells...

however in writing this, i think i may have though of a solution. I wonder if i can set within the calculated fields pulling the the value an additional dimensional value to tie it with. ie. take value and set CG/NCG = ICG.

then both metrics appear in ICG and are calculable...

i'll revert a bit later with whether this worked.

kr