Log In
Register
Home
June 18, 2019
Offline
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
Online
Hi Nadeem,
My guess is that you don't have the date field displayed in the pivot, can you try attaching the file again? Without seeing a sample pivot, hard to say.
June 18, 2019
Offline
Thanks for reply. File is reattached with less data.
November 8, 2013
Online
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)
