

June 18, 2019

The Result of % is accurate when select a single date but when I select multiple dates the result is wrong.
e,g When Date is selected 01-07-2019 then % of 'U1 A' is 9.11% and 'U1 B' is 8.58% and
When 01-07-2019 and 02-07-2019 are selected the % of 'U1 A' is 15.05% and 'U1 B' is 13.00%
While the Accurate % are 7.52% and 6.51%
So, plz help me how I can get it actual percentages
Thanks
(File is attached)


November 8, 2013

Hi Nadeem,
It's more like a math problem, rather than an excel problem.
In your layout,
DocDate | U_ZONE | Dscription | Attribute | Value |
1/07/2019 | U1 A | LARGE PLAIN | sOfftakeQty | 3382 |
1/07/2019 | U1 A | LARGE PLAIN | sReturnQty | 308 |
1/07/2019 | U1 A | LARGE PLAIN | sRetPer | 0.09107 |
2/07/2019 | U1 A | LARGE PLAIN | sOfftakeQty | 3398 |
2/07/2019 | U1 A | LARGE PLAIN | sReturnQty | 202 |
2/07/2019 | U1 A | LARGE PLAIN | sRetPer | 0.059447 |
In day 1, Offtake is 3382, Return is 308. Day2: Offtake: 3398, Return 202.
The pivot will aggregate all values, so if you select both days, all details wil be aggregated: Offtake will be 6782, Return will be 510, but also RetPer will be 0.1505 (0.09107 + 0.059447).
If you want RetPer to be Return / Offtake, you should not add individual percentages, that's wrong. It needs to be calculated again: =510/6782=7.52%
Remove the RetPer from your data, keep Offtake and Return as individual columns, then you will be able to add a pivot table calculated field=Return/Offtake. This will be calculated based on your selections, and not aggregated from the individual calculations.
1 Guest(s)
