I have a query where I am calculating weighted avg.
Rows\Columns | Order Count | Cycle Time | Qty | Cycle Time * Qty | WA
Prod Grp |
order count is CKF of count of order
cycle time is CKF of 365 days divide by order count
Qty is KF material qty
cycle time * qty is CKF multiplication of cycle time and qty
WA is CKF of weighted avg i.e. cycle time * qty / qty
now problem is, WA is getting calculated at detail level properly but at result rows it is messing things up.
e.g. for below records
PLNT1 - PG1 - M1 - 2 - 182.5 - 10 - 1825 - 182.5
PLNT1 - PG1 - M2 - 4 - 91.25 - 10 - 912.5 - 91.25
RESULT 6 - 60.83 - 20 - 2737.5 - 60.83
for the result row it is again taking sum (order count) to calculate cycle time, in this case 365/6 = 60.83, and uses 60.83 to calculate again "Cycle Time * Qty" which result into 1216.6 instead of 2737.5. Now this 1216.6 is divided by 20 and results into 60.83 WA which is wrong. Instead of 60.83 as WA, I want 2737.5 / 20 = 136.875 as WA.
require help to solve the problem,