Sub percentage allocation based on different attributes

1635834
1635834
in Modeling

Hi 

I have one module with dimensions of Index list(Numbered list), time,versions

I have 4 line items

1st- cost center-list formatted

2nd-Planning-list formatted

3rd-Business Area-list formatted

4th-Percentage-Number formatted(%)

so

cost center1-Planning Account1-Business Area1-I will enter %(20%)

cost center1-Planning Account1-Business Area2-I will enter %(80%)

So when I enter the percentage in 2nd combination , it should send me an error in 2nd entry if the sum of percentages with respect to cost centers and planning accounts is more than 100%.

 

Please help me in solution.

 

Thanks

Shirisha.

 

Tagged:

Best Answer

  • ChrisAHeathcote
    ChrisAHeathcote
    Answer ✓

    @1635834 

    To create a formula in which you are able to sum down the input using the criteria of cost centre, planning and business area you need to create a second module contain all three lists as dimensions.

    Create a line item formatted as number.

    Use the following formula = First module.Percentage[SUM:First module.cost centre, SUM:First module.planning, SUM:First module.business area]

    This will sum all the percentages in the source module which satisfy the conditions of the three dimensions. 

    Back in the target create a new line item where you will show the total from this new module. Format it as percentage.

    Use the following formula = Second module.new line item[LOOKUP:Cost Centre, LOOKUP:Planning, LOOKUP:Business area] 

    This will pull across the total percentage which has been calculated in the second module as per the list items select in the first three line items; cost centre, planning and business are.

    Create another line item formatted as number.

    Use the following formula = IF Total > 1 then 1 else 0

    Use this line item as a criteria to set up conditional formatting.

    The following link will run you through the steps involved in setting this up - Conditional formatting in UX 

Categories