How to do use aggregate value?

Options

Hey everyone,

i am stuck at the part where i have to devise a formula for Subtract 1 line item,for that i need the extra value which is 5% in this case ,can be seen in Override-Allocation line item and i need to divide it in those members which do not overriden the values like for LM1,LM2,LM3,LM4 and LM5 which looks like 1.25% for LM1,LM3,LM4,LM5 each and 0% for LM2.So for that i have a count line item which adds upto 4 which when divide 5% extra result in 1.25% but for to put this in a formula.

Check attached screenshot

Answers

  • nvilkko
    Options

    Hi @aishwary2003,

    I am assuming MLM1 is the parent of these LM items.

    1. You need a line item where you check the items where the extra result (1.25%) should be applied. Based on your screenshot this could be NOT 'Override?' or 'Count 1' <> 0.
    2. If I understand correctly, what you want to do is, divide the parent summary (MLM 1) of 'Override-Allocation' with the parent summary of 'Count 1'. You can reference the parent summary by looking up the parent of LM items. You should have a properties module for LM list where you have a line item for the parent. You can use that for the lookup.
    3. Final Formula: IF Extra Result? THEN 'Override-Allocation'[LOOKUP: Parent] / 'Count 1'[LOOKUP: Parent] ELSE 0

    Hope this helps!

    Niko