At one point or another, Microsoft AX developers will come across the need to work with requirements to proportionally allocate total amounts in X++ code by quantity, weight, percentage, and/or other criteria.

There are a variety of methods to accomplish these goals already embedded in the system, but not all of them yield the same results. Each calculated amount should be rounded to two decimal points and allocation should be done with minimum difference.

In the following article, we will consider the four most commonly used allocation methods. These methods are not dependent upon AX version or selection of ERP systems, so this article may be helpful for all ERP developers.

 

Simple proportion and proportion with a difference settlement

Please refer to the example in the table below of an allocation of $100 for 7 separate transactions with quantity = 1 for each transaction.

The first method, called as “Simple proportion”, is based upon the formula:

Amount[i] = AmountTotal * Qty[i] / QtyTotal

After rounding, we will have $100.03 as the result, i.e. there will be the difference of $0.03 between the initial allocated amount and the final allocated amount. The discrepancy in values is unacceptable in most cases.

Methods-of-ProportionalPercentage-Amount-Allocation_1

Methods 2 and 3 are also based upon the same formula, but the total amount is settled to one specific line those being: the last line in Method 2 and to the line with the greatest amount in the Method 3. After using Methods 2 or 3, the total allocated amount is consolidated; unfortunately, the differences between lines with the same quantity can still reach $0.03, which is 3 times more compared to direct rounding ($0.01). These methods work in a number of scenarios, but in other cases we may need a more accurate algorithm.

Side note: Method 2 is more frequently used in standard AX code, i.e. LedgerAllocationProcessRequest.createVoucherOffsets() method:

Methods-of-ProportionalPercentage-Amount-Allocation_2

Remains allocation

Method 4, the last method, is detailed in the table below. This is the method that will yield the most accurate algorithm.

The fundamental principal of Method 4 involves calculating ‘open’ amounts based upon allocation of amount and quantity.

Each time the amount for the line is calculated, deduct the “Qty to allocate” and the “Amount to allocate.” Then, use the newly calculated ‘open’ values for following calculations instead of using the initial values as seen in previous Methods (1-3).

The formula used in Method 4 is:

Amount[i] = AmountRemaining * Qty[i] / QtyRemaining

Instead of:

Amount[i] = AmountTotal * Qty[i] / QtyTotal

Methods-of-ProportionalPercentage-Amount-Allocation_3

This approach is also frequently used in the standard code. This is often seen in inventory or in the AR/AP settlement processes.

Good luck with allocation!