Sum with conditions

mikeng

Hi all,

I'm having trouble with the sum function. I have the reference module [A] as below:

Then I need to sum in Module [B] that only select the "SGA" element in "P&L Total" (which is a list) but I can't. The line item needs to insert the formula is "SGA" in the image below:

 

I've tried all sum, select, lookup, select&sum to insert the ".SGA" but the formula still doesn't work. Please help!

 

Thanks very much!

Best regards,

Mike

JaredDolich

@mikeng 

The reason you can't use SUM is because your target module doesn't have a common dimension to a list formatted line item in your reference module.

The best practice method for what you are trying to do is to use DISCO. In this case we need a system module which is a single module with one dimension, in your case the transaction module or reference module.

 

Calculate once, refer often.

 

Here's an example. I use countries and product but you could use as many dimensions as you want.

The transaction module (reference module) only has the values we need and is keyed on a unique value that identifies the row. If you have a time dimension you should dimensionalize that and it should not be part of your key.

Then we build a system module using the keys of the transaction module.

Now we can use a SUM function in our output module.

Notice how the output module has the same dimension as the list formatted line item in our system module.

And we get the sales to sum on the country dimension!