

November 27, 2020

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.


November 8, 2013

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 8, 2013

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)
