Sum with conditions

Highlighted
Occasional Contributor

Sum with conditions

Hi all,

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

Module [A].JPG

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:

Module [B].JPG

 

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

1 REPLY 1
Highlighted
Master Anaplanner/Community Boss

Re: Sum with conditions

@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.

SumExample001.png

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

SumExample002.pngSumExample003.png

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.

SumExample005.png

SumExample004.png

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


Jared Dolich