Log In
Register
Home
November 27, 2020
Offline
Hi Mynda
I have 3 tables - 1. Trial balance with ledger accounts 2. Master table with ledgers and groupings 3. Budget for group of accounts
I created a profit & loss a/c in pivot table using data model adding the above 3 tables containing actual and budgeted figures.
Actual Budget for remaining months Total for the year
1. Sales XXX XXX XXX
2. Purchases XXX XXX XXX
3. Direct exp XXX XXX XXX
4. Fixed exp XXX XXX XXX
From above rows, I would like to calculate Gross profit (Sales minus purchases minus direct exp) and Net profit (Gross profit minus fixed exp)
In normal pivot table, I am able to get it by using calculated fileds. But in pivot table with data model I am unable to find the calculated fileds. Pls help.
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 Thulasi,
For Gross Profit, should be something like this:
Sales=CALCULATE(SUM(ValuesTable[Actual], ValuesTable[Category]="Sales")
DirExp=CALCULATE(SUM(ValuesTable[Actual]), ValuesTable[Category]="Direct exp")
FixedExp=CALCULATE(SUM(ValuesTable[Actual]), ValuesTable[Category]="Fixed exp")
GP=[Sales]-[DirExp]
NET=[Sales]-[DirExp]-[FixedExp]
November 27, 2020
Offline
Hi Catalin Bombea
Thanks for your reply and suggestion. But I am sorry to say that I am unable to follow it.
Sales, Direct exp, fixed exp are rows in the pivot table. I would like to insert the image of the PT. How to insert it here, pls guide.
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 Thulasi,
Below message editor, there is an Attachments button.
Instead of an image, please upload a sample file with your data structure, it will be easier to work on a real example.
November 27, 2020
Offline
Hi Catalin Bombea
Thanks. I will attach the file for more clarification of my query. Thanks a lot.
November 27, 2020
Offline
Hi Catalin Bombea
I have uploaded file for your reference.
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 Thulasi,
A calculated field in a normal pivot does not add the results below row fields a new rows, it is added as a calculated column.
You can try cube formulas in normal cells, above your pivot:
=GETPIVOTDATA("[Measures].[TOTAL]",$A$1,"[Masters].[PrimaryGroup]","[Masters].[PrimaryGroup].&[Sales Accounts]")
=GETPIVOTDATA("[Measures].[TOTAL]",$A$1,"[Masters].[PrimaryGroup]","[Masters].[PrimaryGroup].&[Duty Drawback]")
combine such formulas to make your totals calculation
1 Guest(s)
