Excel Forum

How to get Accurate % after unpivot data in Pivot.

Nadeem Baig

Member

Members

Forum Posts: 9

Member Since:
June 18, 2019

Offline

July 24, 2019 - 5:01 pm

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)

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

July 25, 2019 - 4:47 am

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.

Nadeem Baig

Member

Members

Forum Posts: 9

Member Since:
June 18, 2019

Offline

July 25, 2019 - 3:50 pm

Thanks for reply. File is reattached with less data.

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

July 26, 2019 - 1:10 am

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.