Finance - P&L Tracking – Customised & Nested Pivot Tables
Finance - P&L Tracking – Customised & Nested Pivot Tables
In this sample, we track Actuals against Budgeted amounts, for each P&L line, showing variances too, in a Pivot Table. Simple Pivot Tables are relatively easy to create and use, but for this file we add the condition of also needing to show the additional lines such as ‘Gross Profit’, ‘Operating Income’, which do not appear in the source GL data - in effect we need to create a highly-customised customised Pivot Table.
Data Inputs
For this sample, we take the following data inputs:
Dimensions
The dimension tables will be determined by the current structure of the business
![](images/finance-pnl-tracking-custom-pivot-1-data.png)
Accounts Structure
This is where we define the layout and mappings of the P&L lines to different Headings and Sub Accounts
![](images/finance-pnl-tracking-custom-pivot-2-data.png)
![](images/finance-pnl-tracking-custom-pivot-3-data.png)
General Ledger
The GL here contains both the Actuals and Budgeted data for each month, so both datasets are already available to us in a nice, easy to work with format, in the same table.
![](images/finance-pnl-tracking-custom-pivot-4-data.png)
P&L Tracking Measures
The following specific measures are shown:
- Actuals
- Budget
- Variance $
- Variance %
Dashboard - Snippet
Unlike most of our other samples, the final output here in presented in Excel itself, rather than through a Power BI dashboard – which is great for visuals, but not as strong with tables. When it comes to just pivot tables, it is a matter of preference.
The final file can also be downloaded here, if you want to review the formulae in detail: Download Sample
![](images/finance-pnl-tracking-custom-pivot-1-db.png)
Click to view large-sized image
Data Model - Snippet
The final data model used is shown here:
![](images/finance-pnl-tracking-custom-pivot-1-dm.png)
Click to view large-sized image
Measures
SUM GL Amt
SUM GL Amt :=
SUM ( 'General_Ledger'[Amount] )
GL Amt (Correct Signs)
GL Amt (Correct Signs) :=
SUMX ( Accounts, [SUM GL Amt] * Accounts[Report Sign] )
Actual Amt
Actual Amt :=
CALCULATE ( [GL Amt (Correct Signs)], Scenarios[ScenarioName] = "Actual" )
Rev Act
Rev Act :=
CALCULATE ( [Actual Amt], Headers[Header] = "Revenue" )
CoS Act
CoS Act :=
CALCULATE ( [Actual Amt], Headers[Header] = "Cost of Sales" )
GP Act
GP Act :=
[Rev Act] + [CoS Act]
OpEx Act
OpEx Act :=
CALCULATE ( [Actual Amt], Headers[Header] = "Operating Expenses" )
OI Act
OI Act :=
[GP Act] + [OpEx Act]
OI&E Act
OI&E Act :=
CALCULATE ( [Actual Amt], Headers[Header] = "Other Income & Expense" )
D&A Act
D&A Act :=
CALCULATE ( [Actual Amt], Headers[Header] = "Depreciation & Amortization" )
II&E Act
II&E Act :=
CALCULATE ( [Actual Amt], Headers[Header] = "Interest Income/Expense" )
Taxes Act
Taxes Act :=
CALCULATE ( [Actual Amt], Headers[Header] = "Taxes" )
NI Act
NI Act :=
[OI Act] + [OI&E Act] + [D&A Act] + [II&E Act] + [Taxes Act]
IS Subtotal Act
IS Subtotal Act :=
IF (
COUNTROWS ( VALUES ( Headers[Header] ) ) = 1,
SWITCH (
VALUES ( Headers[Header ID] ),
1, [Rev Act],
2, [CoS Act],
3, [GP Act],
4, [OpEx Act],
5, [OI Act],
6, [OI&E Act],
7, [D&A Act],
8, [II&E Act],
9, [Taxes Act],
10, [NI Act],
BLANK ()
),
BLANK ()
)
Actual Total
Actual Total :=
IF (
MAX ( Headers[Summary] ) = 1
&& (
MAX ( Headers[Show Detail] ) = 1
|| COUNTROWS ( VALUES ( Subheader[Subheader] ) ) > 1
),
[IS Subtotal Act],
[Actual Amt]
)
Budget Amt
Budget Amt :=
CALCULATE ( [GL Amt (Correct Signs)], Scenarios[ScenarioName] = "Budget" )
Rev Bud
Rev Bud :=
CALCULATE ( [Budget Amt], Headers[Header] = "Revenue" )
CoS Bud
CoS Bud :=
CALCULATE ( [Budget Amt], Headers[Header] = "Cost of Sales" )
GP Bud
GP Bud :=
[Rev Bud] + [CoS Bud]
OpEx Bud
OpEx Bud :=
CALCULATE ( [Budget Amt], Headers[Header] = "Operating Expenses" )
OI Bud
OI Bud :=
[GP Bud] + [OpEx Bud]
OI&E Bud
OI&E Bud :=
CALCULATE ( [Budget Amt], Headers[Header] = "Other Income & Expense" )
D&A Bud
D&A Bud :=
CALCULATE ( [Budget Amt], Headers[Header] = "Depreciation & Amortization" )
II&E Bud
II&E Bud :=
CALCULATE ( [Budget Amt], Headers[Header] = "Interest Income/Expense" )
Taxes Bud
Taxes Bud :=
CALCULATE ( [Budget Amt], Headers[Header] = "Taxes" )
NI Bud
NI Bud :=
[OI Bud] + [OI&E Bud] + [D&A Bud] + [II&E Bud] + [Taxes Bud]
IS Subtotal Bud
IS Subtotal Bud :=
IF (
COUNTROWS ( VALUES ( Headers[Header] ) ) = 1,
SWITCH (
VALUES ( Headers[Header ID] ),
1, [Rev Bud],
2, [CoS Bud],
3, [GP Bud],
4, [OpEx Bud],
5, [OI Bud],
6, [OI&E Bud],
7, [D&A Bud],
8, [II&E Bud],
9, [Taxes Bud],
10, [NI Bud],
BLANK ()
),
BLANK ()
)
Budget Total
Budget Total :=
IF (
MAX ( Headers[Summary] ) = 1
&& (
MAX ( Headers[Show Detail] ) = 1
|| COUNTROWS ( VALUES ( Subheader[Subheader] ) ) > 1
),
[IS Subtotal Bud],
[Budget Amt]
)
Variance $
Variance $ := [Actual Total] - [Budget Total],
Variance %
Variance % := DIVIDE ( [Variance $], [Budget Total] )
Feedback
Submit and view feedback