Weighted Average sub-total not correct despite correct calculation?

[shanestocks]

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.

[v-yaningy-msft]

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

[shanestocks]

Hi 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?