Formula for subtotal with conditions, in Pivot with Calculation - Fields (Pivot - Analyze - Calculation - Fields - subtotal by condition IF or SUMIF)

[newton_iss]

This is Pivot table. Field1 is counted as Bottle price / Volume. But Subtotal for Field 1 is wrong.

21,2654321 is wrong answer, watching data, we understand that price of Bottle per Lt is 65.
For some reason, Field 1 is taking all volume.

Perfect Formula would look like this: IFERROR('Bottle Price'/SUMIF(Pack;"Bottle";Volume);0), but it doesent work in calculated fields formela.


[TABLE="width: 733"]
<colgroup><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Brand[/TD]
[TD]Pack[/TD]
[TD]SKU[/TD]
[TD]Sum of Volume lt[/TD]
[TD]Sum of Bottle Price[/TD]
[TD]Sum of CAN price[/TD]
[TD]Sum of Field1[/TD]
[TD]Sum of Field2[/TD]
[/TR]
[TR]
[TD]Bud[/TD]
[TD]Bottle[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bud[/TD]
[TD]Bottle[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1060[/TD]
[TD="align: right"]68900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bud[/TD]
[TD]Bottle[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1120[/TD]
[TD="align: right"]72800[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bud[/TD]
[TD="colspan: 2"]Bottle Total[/TD]
[TD="align: right"]3180[/TD]
[TD="align: right"]206700[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bud[/TD]
[TD]Keg[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1020[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bud[/TD]
[TD]Keg[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1080[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bud[/TD]
[TD]Keg[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1140[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bud[/TD]
[TD="colspan: 2"]Keg Total[/TD]
[TD="align: right"]3240[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Bud[/TD]
[TD]CAN[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1040[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]91520[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD]Bud[/TD]
[TD]CAN[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]96800[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD]Bud[/TD]
[TD]CAN[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1160[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]102080[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD]Bud[/TD]
[TD="colspan: 2"]CAN Total[/TD]
[TD="align: right"]3300[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]290400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD]Bud Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9720[/TD]
[TD="align: right"]206700[/TD]
[TD="align: right"]290400[/TD]
[TD="align: right"]21,2654321[/TD]
[TD="align: right"]29,87654321[/TD]
[/TR]
[TR]
[TD]Corona Extra[/TD]
[TD]Bottle[/TD]
[TD]Dark[/TD]
[TD="align: right"]1180[/TD]
[TD="align: right"]76700[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Corona Extra[/TD]
[TD]Bottle[/TD]
[TD]Light[/TD]
[TD="align: right"]1220[/TD]
[TD="align: right"]79300[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Corona Extra[/TD]
[TD="colspan: 2"]Bottle Total[/TD]
[TD="align: right"]2400[/TD]
[TD="align: right"]156000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Corona Extra[/TD]
[TD]CAN[/TD]
[TD]Dark[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]105600[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD]Corona Extra[/TD]
[TD]CAN[/TD]
[TD]Light[/TD]
[TD="align: right"]1240[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]109120[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD]Corona Extra[/TD]
[TD="colspan: 2"]CAN Total[/TD]
[TD="align: right"]2440[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]214720[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Corona Extra Total[/TD]
[TD][/TD]
[TD="align: right"]4840[/TD]
[TD="align: right"]156000[/TD]
[TD="align: right"]214720[/TD]
[TD="align: right"]32,23140496[/TD]
[TD="align: right"]44,36363636[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14560[/TD]
[TD="align: right"]362700[/TD]
[TD="align: right"]505120[/TD]
[TD="align: right"]24,91071429[/TD]
[TD="align: right"]34,69230769[/TD]
[/TR]
</tbody>[/TABLE]