Ratio calculation not executed properly in Total part of Column

[SchalkF]

Hi Guru's

I have a situation where the calculation is not correct and I am picking it up in the Total part of my straight table.

It might be a simple fix, but I am not sure.

This is a breakdown of the line item to its core components, where the 1st Base Item makes up 90% of the final product, and the 2nd Base Item makes up 10%.

Item Quantity - is the original quantity sold on the line item (70 units)

Primary Qty - amount of the Base Item that goes into the final product

Ratio Calculation:
      SUM(AGGR(
      Sum(BPrimaryQty)
     ,[Document Number],[Act Line Num],[Item Code],BItemCode))
/
     SUM(AGGR(nodistinct
     Sum(BPrimaryQty)
     ,[Document Number],[Act Line Num],[Item Code]))

Baseprice:

     Sum(distinct prim_basePrice)

TargetFinalPrice:

 (
       SUM(AGGR(
       Sum(BPrimaryQty)
       ,[Document Number],[Act Line Num],[Item Code],BItemCode))
    /
       SUM(AGGR(nodistinct
       Sum(BPrimaryQty)
       ,[Document Number],[Act Line Num],[Item Code]))
 )
*
    SUM(distinct prim_basePrice)

This works fine for the line item because the Recipe Ratio * Baseprice is used correctly, 90% of 28 000, and 10% of 150 000.  But for the Total calculation it is using 100% of 178 000.  I hope this makes sense.

How can I change my calculation to first execute the line item calculation and then the Total line calculation?

Thanks in advance

Schalk

[SchalkF · Accepted solution]

I resolved my own issue.

I required another Aggr with BItemCode, as below:

Sum(Aggr((
SUM(AGGR(
Sum(BPrimaryQty)
,[Document Number],[Act Line Num],[Item Code],BItemCode))
/
SUM(AGGR(nodistinct
Sum(BPrimaryQty)
,[Document Number],[Act Line Num],[Item Code]))
)
, [Document Number],[Act Line Num],[Item Code],BItemCode))