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.
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]
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.
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.
Hi Catalin Bombea
Thanks. I will attach the file for more clarification of my query. Thanks a lot.
Hi Catalin Bombea
I have uploaded file for your reference.
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
Thanks. Let me try.
