Great to hear you've found my videos helpful!

You can add a calculated field and only use it in one PivotTable. However, calculated items will be available in all PivotTables where the related field is present. The only way to prevent this is to create a separate Pivot cache, which means you cannot connect this PivotTable to any of the other Slicers and I suspect that's not what you want.

Mynda

Dear Mynda 

Really too much appreciated your reply and helping me.

Please, the link you mentioned includes a note at the far top: Note: This does not apply to Power Pivot PivotTables

Does this means creating a query using power query, then saving and loading as a connection, then using this connection as a data source for the pivot table, does this will solve the issue?

Or do I still not understand it correctly?

Thanks a lot for your valuable time.

No, the link is explaining what a Pivot Cache is so that you have a better understanding of how PivotTables work in the background.

There's no solution to what you want. It's simply not possible to have calculated items for a field present in some PivotTables and not present in others unless you omit the field that owns the calculated item from the PivotTable altogether. Calculated fields are like any other field, you can include it in a PivotTable or not, it makes no difference.

I think it would be best if you provide a sample file so we can see what your ideal solution is based on some of your data. We can then either give you a workaround or explain in the context of your file why it's not possible.

Mynda

No, the link is explaining what a Pivot Cache is so that you have a better understanding of how PivotTables work in the background.

Thanks a lot for the detailed explanations.

There's no solution to what you want. It's simply not possible to have calculated items for a field present in some PivotTables and not present in others unless you omit the field that owns the calculated item from the PivotTable altogether. Calculated fields are like any other field, you can include it in a PivotTable or not, it makes no difference.

So, if the calculated item depends on the field it belongs to, then adding a calculated field to a pivot table and removing it from another copy of the same pivot table, then adding the calculated item to the existing calculated field might be a workaround, isn't it?

I think it would be best if you provide a sample file so we can see what your ideal solution is based on some of your data. We can then either give you a workaround or explain in the context of your file why it's not possible.

My native language is Arabic, so the file is in Arabic, please let me translate a sample and upload it here.

Hi,

I found an idea to do the calculation on the source itself.

I've attached the sample file.

I mean the rows where it says:

Balance Sheet Totals

Total Non-Current asset
Total Current Assets
Total Assets
Total Current Liabilities
Total Non-Current Liabilities
Total Liabilities
Total Owners Equity
Total Liabilities & Owners Equity

You are right as no way to do what I want.

Thanks

Thanks for the file, but it's not clear what you want. Also, the data in the file is not in the correct layout for a PivotTable, which might be why you're having trouble. Please watch these tutorials:

PivotTables Made Easy and Why They go Wrong

PivotTable Profit and Loss

If you still have questions, please come back with your sample file showing your source data and a mock up of your desired result.

Mynda

Thanks a lot for your valuable time in replying to my message.

Here are the exact details of what I am doing.

I've attached 2 files for what I am doing and what I want to achieve.

-------

1. The file OriginalFile-No PowerQuery.xlsx represent the raw data I receive each year for each company, and I've about 85 companies, and each company has 3 financial statements (balance sheet, income, and cache flow), and I've only included one company and 2 financial statements for clarifications only.
2. The raw data only exists in the 1st sheet of the file OriginalFile-No PowerQuery.xlsx, the sheet titled (1.RawDataIReceive).
3. All the other sheets to the rights inside the workbook are created by me each year, and that includes:
   

   1stAnalysis-Vertical

   2ndAnalysis-Horizontal

   3rdAnalysis-Ratio(Amount)

   4thAnalysis-Ratio(Times)

   5thAnalysis-Ratio(%)

   Then I aggregate everything in the last sheet of the same file OriginalFile-No PowerQuery.xlsx, the sheet titled (7.ALL).

-------

1.  The file OriginalFile PowerQuery.xlsx includes the work I've done on power query from the last sheet titled (7.ALL).

-------

What I would like to do is to do all calculations on power query, I mean the:

• Vertical.
• Horizontal.
• Ratio (Amount).
• Ratio (Times).
• Ratio (%).

---------

The reason for my request is that you can imagine one big excel file that includes 3 statements for each company, and an analysis of everything the way I did, as I found myself running crazy.

So I thought to just do it in steps in power query depending on the raw data only.

So is it possible to import raw data in power query and then:

• Set a column for the balance that exist in the raw data.
• Set a column for Vertical analysis calculation values.
• Set a column for Horizontal analysis calculation values.
• Set a column for Ratio (Amount) analysis calculation values.
• Set a column for Ratio (Times) analysis calculation values.
• Set a column for Ratio (%) analysis calculation values.

This is what I was looking for and thinking about.

----------

Or please tell me if you think I can make a workbook for each company and do all calculations manually to avoid mistakes and then combine or merge them all in Power Query later on.

Please advise.

Check the video here for more information:

https://www.youtube.com/watch?v=DT2ZwB1XyLE

Thanks a lot and I do hope to achieve it as I am really suffering from such a thing.

Thanks a lot for your valuable time helping me.

Hi mgbsher,

For the type of analysis you want, you should really use Power Pivots, not calculated items/fields. 

Please refer to your other post, as the subject is identical: https://www.myonlinetraininghub.com/excel-forum/power-query/how-to-divide-all-items-in-a-column-by-an-item-included-in-the-same-column-then-add-results-to-new-column#p26946

Thanks a lot for your valuable time reading my messages and helping me.

I will follow your advice, my friend.

Thanks 🙂