Log In
Register
Home
December 13, 2021
Offline
Hi,
I've watched your various dashboard videos on YouTube, really lovely and useful 🙂 so thanks indeed.
The main technique you used in these videos depended on copying one pivot table several times and adjusting it and its pivot chart according to your needs, and after adjustments, you were adding it to the dashboard and connected all slicers to all pivot tables so everything can be work dynamically.
In case I want to add a calculated field or a calculated item to one of these copied pivot tables and do not want it to be added to other pivot tables, how to do so? given that all pivot tables need to be connected, and all created slicers need to be connected as well.
Thanks
July 16, 2010
Offline
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
December 13, 2021
Offline
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.
July 16, 2010
Offline
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
December 13, 2021
Offline
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.
December 13, 2021
Offline
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
July 16, 2010
Offline
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
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
December 13, 2021
Offline
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.
-------
- 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.
- The raw data only exists in the 1st sheet of the file OriginalFile-No PowerQuery.xlsx, the sheet titled (1.RawDataIReceive).
- 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).
-------
- 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?.....T2ZwB1XyLE
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.
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
November 8, 2013
Offline
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.myonlinetraininghu.....umn#p26946
1 Guest(s)
