Sum with conditions

mikeng
mikeng
in Modeling

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

Tagged:

Answers

  • JaredDolich
    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.

    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!

Categories