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