Contributor

## Sub percentage allocation based on different attributes

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

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

Thanks

Shirisha.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Boss

## Re: Sub percentage allocation based on different attributes

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

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
Community Boss

## Re: Sub percentage allocation based on different attributes

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

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA