Pivot with separate Calculations

Hi All,

I made an pivot , and column O,P,Q,R are not under the pivot but additionally created for some calculation purpose.

But when i tried to filter the data in pivot then the calculations goes wrong see below screenshots.
In below first screenshot, its showing the correct calculations (since it is having all data shown.i.e. M8/G8=Type 2Hours/Base work ....)

1st.JPG

When tried to search for particular person then the calculation goes wrong.(i.e. the cell gets changed but formula remained to be M8/G8 , but in contrary to this correct calculations should be L8/G8 i.e. Type 2 / Base work....)

2nd.JPG


Can you please suggest how to fix this issue?

Pivot Table range and cell range behaves separately.

I.E. If data location changes in pivot due to slicer/filter etc, cell reference will not dynamically change with it in formula.

Since you have Office 365 Pro Plus, I'd recommend using Get & Transform (PowerQuery) to load the data to Data Model, then using DAX measures to calculate things.

If you need more specific help, I'd recommend uploading desensitized sample of your workbook (including source data and pivot table, along with sample calculations for desired result).

To upload, use "Go Advanced" button and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Originally Posted by harpreet_1990

Please let me know if you are available for skype call or something more convenient.
Thank You.

Please do not ask members to contact you privately outside this forum. The contents are for the benefit of all, and keeping questions on the forum gets you a better chance of finding answers
Thanks