I have one module with dimensions of Index list(Numbered list), time,versions
I have 4 line items
1st- cost center-list formatted
3rd-Business Area-list formatted
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.
Go to Solution.
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