SAC Data Action - Calculating Allocation % is not summing to 100%

[zee_siddique15]

Hi All ,

1) I have the Kgs produced (Decimal to 7 places) measure to store the data at Farm and Product level, Material = "#" and Customer = "#" (input by users)

2) I have the Kgs Sold (Decimal to 7 places) measure to store the data at Farm = "#" and Product , Material and Customer level (input by users)

3) Price ($/Kg) entered at Farm = "#" and Product, Material and Customer level (input by users) 

3) Data Action Starts:- 

Step 1: - Calculates the Allocation % based on the Kg produced data (you can see that the data is not summing up to 100%

2) Step 2:- Calculate the Sales Qty (Trays) = Sales Qty (Kgs) / Kgs to Tray Conversion Factor 

3) Step 3:- Calculate the Sales Qty (Pallets) = Sales Qty (Kgs) / Kgs to Pallet Conversion Factor 

4) Step 4:-  Calculate the Sales Revenue ($) = Sales Qty (Kgs) * Price ($/Kg)

5) Step 5:- Bringing down the Sales Qty (Kgs/Trays/Pallets/Revenue) pushed down from Farm = # to individual Farms (Farm 1/2/3/4) based on the Allocation %s calculated in Step 1 of the data action.

the Allocated data doesn't come down to round up to the because the allocation % are not 100% .

is there any way I can have the allocation % to 100%.

[N1kh1l · Accepted solution]

@zee_siddique15 

Because you Kg's produced data is already with 7 decimals, when you calculate the Allocation weight, it will require more than 7 decimal precision to get an exact sum of 1. In Excel its summing to 100% or 1 because you have more than 7 decimal precision there. Now SAC has 7 decimal precision for their measure so there is no way we can increase it. May be try to calculate the Residual amount which is not allocated and that will be 0.0000002 * Allocation Amount. You can re allocate this amount on existing weight and see if that helps. I tried it on excel and it seems to be working.

Basically I am re allocating the residual Allocation amount (0.0000002 * 1666.666667 =0.0003333) using the same weight and then adding the original Allocated amount to the residual amount. I intentionally took the amount with decimals to see if it creates precision issue.

Hope this helps !!

Nikhil

[zee_siddique15 · Accepted solution]

Hi @N1kh1l , Thanks the suggestion did work. Thanks for your help