Forum Discussion
Helper IWeighted Average sub-total not correct despite correct calculation?
Hi guys, I have a very simple task of finding a weighted average price of a "P CODE", which is determined by the use of "M CODE".
Please see below Power Pivot:
| P Code | Meat Code | Act. (EUR) |
| P10000 | 10082633 | 4.851 |
| P10000 | RM0023 | 5.460 |
| P10000 | RM0074 | 4.007 |
| P10000 | 5.05368 |
Each meat code has it's own actual price, and the P code should represent the price of the meat * the quantity of the meat used. The meat codes are fine, but the P10000 total is not right. It's close, but it isn't right, and based on my calculations it should be 5.053749.
The raw data is as follows:
| p_code | m_code | meat_price | qty_used |
| P10000 | RM0074 | 4.007 | 1,696 |
| P10000 | RM0074 | 4.007 | 1,539 |
| P10000 | RM0074 | 4.007 | 1,537 |
| P10000 | RM0074 | 4.007 | 1,534 |
| P10000 | RM0074 | 4.007 | 1,524 |
| P10000 | RM0074 | 4.007 | 977 |
| P10000 | RM0074 | 4.007 | 1,552 |
| P10000 | 10082633 | 4.580 | 1,561 |
| P10000 | RM0074 | 4.007 | 1,608 |
| P10000 | RM0074 | 4.007 | 1,484 |
| P10000 | RM0074 | 4.007 | 1,465 |
| P10000 | RM0074 | 4.007 | 1,083 |
| P10000 | RM0074 | 4.007 | 1,570 |
| P10000 | RM0074 | 4.007 | 1,101 |
| P10000 | 10082633 | 4.580 | 1,059 |
| P10000 | 10082633 | 4.580 | 1,475 |
| P10000 | 10082633 | 4.580 | 608 |
| P10000 | 10082633 | 4.580 | 1,076 |
| P10000 | 10082633 | 4.580 | 781 |
| P10000 | 10082633 | 4.580 | 1,097 |
| P10000 | 10082633 | 4.580 | 1,046 |
| P10000 | RM0074 | 4.007 | 551 |
| P10000 | RM0074 | 4.007 | 1,041 |
| P10000 | RM0074 | 4.007 | 1,583 |
| P10000 | RM0074 | 4.007 | 548 |
| P10000 | 10082633 | 4.580 | 1,601 |
| P10000 | 10082633 | 4.580 | 1,042 |
| P10000 | 10082633 | 4.580 | 1,065 |
| P10000 | RM0023 | 5.762 | 1,069 |
| P10000 | RM0023 | 5.762 | 1,093 |
| P10000 | 10082633 | 4.580 | 1,047 |
| P10000 | 10082633 | 4.580 | 1,063 |
| P10000 | 10082633 | 4.580 | 1,056 |
| P10000 | 10082633 | 4.580 | 1,017 |
| P10000 | 10082633 | 4.580 | 1,037 |
| P10000 | 10082633 | 4.580 | 1,617 |
| P10000 | 10082633 | 4.580 | 1,053 |
| P10000 | 10082633 | 4.580 | 1,636 |
| P10000 | 10082633 | 4.580 | 1,644 |
| P10000 | 10082633 | 4.580 | 1,639 |
| P10000 | 10082633 | 4.580 | 1,493 |
| P10000 | 10082633 | 4.580 | 1,079 |
| P10000 | 10082633 | 4.580 | 443 |
| P10000 | 10082633 | 4.580 | 1,579 |
| P10000 | RM0023 | 5.762 | 556 |
| P10000 | 10082633 | 4.580 | 937 |
| P10000 | RM0023 | 5.762 | 1,119 |
| P10000 | 10082633 | 4.580 | 544 |
| P10000 | 10082633 | 4.580 | 1,054 |
| P10000 | 10082633 | 4.580 | 543 |
| P10000 | 10082633 | 5.940 | 1,023 |
| P10000 | 10082633 | 5.940 | 1,034 |
| P10000 | 10082633 | 5.940 | 504 |
| P10000 | 10082633 | 5.940 | 469 |
| P10000 | RM0023 | 6.240 | 1,003 |
| P10000 | 10082633 | 5.940 | 1,086 |
| P10000 | RM0023 | 6.105 | 998 |
| P10000 | RM0023 | 6.105 | 995 |
| P10000 | RM0023 | 6.105 | 1,495 |
| P10000 | RM0023 | 6.105 | 998 |
| P10000 | RM0023 | 6.105 | 502 |
| P10000 | RM0023 | 6.105 | 1,001 |
| P10000 | RM0023 | 6.060 | 738 |
| P10000 | RM0023 | 6.060 | 498 |
| P10000 | RM0023 | 6.060 | 502 |
| P10000 | RM0023 | 6.060 | 502 |
| P10000 | RM0023 | 6.060 | 1,003 |
| P10000 | RM0023 | 6.060 | 502 |
| P10000 | RM0023 | 6.060 | 1,003 |
| P10000 | RM0023 | 6.060 | 502 |
| P10000 | RM0023 | 6.060 | 1,003 |
| P10000 | RM0023 | 6.060 | 997 |
| P10000 | RM0023 | 6.060 | 998 |
| P10000 | RM0023 | 6.060 | 300 |
| P10000 | RM0023 | 6.060 | 502 |
| P10000 | RM0023 | 6.060 | 1,003 |
| P10000 | RM0023 | 6.060 | 995 |
| P10000 | RM0023 | 6.060 | 499 |
| P10000 | RM0023 | 6.060 | 897 |
| P10000 | RM0023 | 6.044 | 1,003 |
| P10000 | RM0023 | 6.044 | 1,003 |
| P10000 | RM0023 | 6.044 | 1,000 |
| P10000 | RM0023 | 6.044 | 1,003 |
| P10000 | RM0023 | 6.044 | 498 |
| P10000 | RM0023 | 6.044 | 510 |
| P10000 | RM0023 | 6.044 | 511 |
| P10000 | RM0023 | 6.044 | 511 |
| P10000 | RM0023 | 6.044 | 502 |
| P10000 | RM0023 | 6.044 | 502 |
| P10000 | RM0023 | 6.044 | 502 |
| P10000 | RM0023 | 6.044 | 502 |
| P10000 | RM0023 | 6.044 | 510 |
| P10000 | RM0023 | 6.060 | 1,503 |
| P10000 | RM0023 | 6.060 | 502 |
| P10000 | RM0023 | 6.060 | 1,008 |
| P10000 | RM0023 | 6.060 | 511 |
| P10000 | RM0023 | 6.060 | 1,003 |
| P10000 | RM0023 | 6.060 | 1,003 |
| P10000 | RM0023 | 6.060 | 511 |
| P10000 | RM0023 | 6.060 | 510 |
| P10000 | RM0023 | 6.060 | 1,003 |
| P10000 | RM0023 | 5.363 | 1,003 |
| P10000 | RM0023 | 5.363 | 1,005 |
| P10000 | RM0023 | 5.363 | 511 |
| P10000 | RM0023 | 5.363 | 752 |
| P10000 | RM0023 | 5.363 | 1,008 |
| P10000 | RM0023 | 5.363 | 1,003 |
| P10000 | 10082633 | 5.940 | 978 |
| P10000 | 10082633 | 5.940 | 532 |
| P10000 | RM0023 | 5.167 | 1,083 |
| P10000 | RM0023 | 5.167 | 1,037 |
| P10000 | RM0023 | 5.167 | 903 |
| P10000 | RM0023 | 5.167 | 1,008 |
| P10000 | RM0023 | 5.167 | 511 |
| P10000 | RM0023 | 5.167 | 797 |
| P10000 | 10082633 | 5.940 | 521 |
| P10000 | 10082633 | 5.940 | 522 |
| P10000 | RM0023 | 5.167 | 903 |
| P10000 | 10082633 | 4.980 | 897 |
| P10000 | RM0023 | 4.913 | 1,003 |
| P10000 | RM0023 | 4.913 | 1,503 |
| P10000 | RM0023 | 4.913 | 985 |
| P10000 | RM0023 | 4.913 | 1,003 |
| P10000 | RM0023 | 4.913 | 933 |
| P10000 | 10082633 | 4.980 | 988 |
| P10000 | RM0023 | 4.876 | 978 |
| P10000 | 10082633 | 4.980 | 1,012 |
| P10000 | RM0023 | 4.876 | 1,203 |
| P10000 | RM0023 | 4.876 | 1,198 |
| P10000 | RM0023 | 4.876 | 782 |
| P10000 | RM0023 | 4.876 | 1,203 |
| P10000 | RM0023 | 4.876 | 787 |
| P10000 | RM0023 | 4.869 | 541 |
| P10000 | RM0023 | 4.869 | 1,203 |
| P10000 | RM0023 | 4.869 | 1,003 |
| P10000 | RM0023 | 4.869 | 1,203 |
| P10000 | 10082633 | 4.980 | 1,056 |
| P10000 | RM0023 | 4.869 | 1,203 |
| P10000 | 10082633 | 4.980 | 1,993 |
| P10000 | RM0023 | 4.869 | 1,582 |
| P10000 | 10082633 | 4.980 | 1,190 |
| P10000 | RM0023 | 4.847 | 782 |
| P10000 | RM0023 | 4.847 | 782 |
| P10000 | RM0023 | 4.847 | 802 |
| P10000 | RM0023 | 4.847 | 406 |
| P10000 | RM0023 | 4.847 | 812 |
| P10000 | RM0023 | 4.847 | 782 |
| P10000 | RM0023 | 4.847 | 812 |
| P10000 | RM0023 | 4.847 | 401 |
| P10000 | RM0023 | 4.805 | 406 |
| P10000 | 10082633 | 4.980 | 396 |
| P10000 | RM0023 | 4.805 | 596 |
| P10000 | 10082633 | 4.980 | 1,163 |
| P10000 | RM0023 | 4.805 | 752 |
| P10000 | 10082633 | 4.980 | 752 |
| P10000 | RM0023 | 4.805 | 1,203 |
| P10000 | RM0023 | 4.805 | 812 |
| P10000 | 10082633 | 4.980 | 1,168 |
| P10000 | 10082633 | 4.980 | 1,131 |
| P10000 | RM0023 | 4.688 | 1,203 |
| P10000 | RM0023 | 4.688 | 1,166 |
| P10000 | RM0023 | 4.688 | 802 |
| P10000 | RM0023 | 4.688 | 1,203 |
| P10000 | RM0023 | 4.688 | 782 |
| P10000 | 10082633 | 4.980 | 1,136 |
And the measure I am using is:
=DIVIDE([req_cost_of_order],[used (KG)])
req_cost_of_order is SUMX of meat_price and qty_used.
used (KG) is SUM of qty_used.
Many thanks everyone.
2 Replies
Hi, shanestocks
According to your description, you want to get a weighted average price of a "P CODE",but the difficulty is that it's hard to understand how your requirement is realized, you can describe your requirement logically and completely (how the number 5.05368 is obtained), post the related MEASUREMENT (([REQ_COST_ OF_ order],[used (KG)).
Best Regards,
Yang
Community Support Team
shanestocksHi Yang, the measure is as follows:
=DIVIDE([req_cost_of_order],[used (KG)])
"req_cost_of_order" is a calculated column which multiplies qty_used * meat_price
"used (KG)" is a SUM of the qty_used column.
I also have two other columns that seperate the P CODE and the M CODE with the above table being a key that merges them both together.
I am thinking that at the P CODE level it should be the weighted average, and at the M CODE level it should simply be the meat_price?
