Pivot table calculation field results

[Anbuselvam K]

hi

Please find the attached copy of excel sheet with pivot table data which is showing two different % of revenue from the same data source and value. Can you check How it's calculated the both value and guide me to get the correct answer?

 

[Chihiro]

So Rev field uses...
=('Sales Value'-'Total Sales Cost')/'Sales Value'

Rev New uses...
=((('Desp. Qty. in Ltr.'*'Selling Price / Ltrs. ')-('Desp. Qty. in Ltr.'*Cost))/('Desp. Qty. in Ltr.'*'Selling Price / Ltrs. '))

In regular excel formula each translates to...

Rev
=(SUM('Sales Value')-SUM('Total Sales Cost'))/SUM('Sales Value')

Rev New
=((SUM('Desp. Qty. in Ltr.')*SUM('Selling Price / Ltrs. '))-(SUM('Desp. Qty. in Ltr.')*SUM(Cost)))/(SUM('Desp. Qty. in Ltr.')*SUM('Selling Price / Ltrs. '))

This is where the issue lies.

Rev New formula should be.
=(SUMPRODUCT('Desp. Qty. in Ltr.','Selling Price / Ltrs. ')-SUMPRODUCT('Desp. Qty. in Ltr.',Cost))/SUMPRODUCT('Desp. Qty. in Ltr.','Selling Price / Ltrs. ')

However, PivotTable calculated field works by doing SUM of each column (based on row label grouping) before any other calculations are performed. Giving unexpected result.

Therefore Rev is the correct value.

 

[Anbuselvam K]

Thanks a lot for your support!