Power BI P&L
Something standard and repeatable
P&L statements are something that I've done (or helped do) a few times now, but it's been tricky to get a good, repeatable, system.
Here I present a system which is repeatable. For each type of report (P&L, Balance Sheet):
- An Excel-based mapping file that is entirely correspondent to how it will look on the screen in Power BI
- One measure which will handle calculation of every line item in the report
- Handles levels of grouping and indentation without structuring it as "level 1", "level 2", etc.
- Several Power Query scripts that can be dropped in with minimal modification
I consider these a big win already, especially as the structure is visually correspondent with the "written" report, and you don't have to convert that structure into something rigid and tabular. Also, because the structure provides a good skeleton for the logic to traverse along, the calculation time is near-instant when surfaced in Power BI.
Excel mapping file
Table structure
It all starts with the Excel mapping file. This is the bedrock of everything that follows, and needs to be manually created. This file needs to look like this:
| RowID | ParentRowID | IndentLevel | Label | RowType | SumAdd | SumSubtract | IsBold | IsItalic | MainAccountID | LabelDisplay | SignOverride | |-------|----------------|-------------|----------------------------------------------------|-------------|----------------|----------------|--------|----------|---------------|----------------------------------------------------|--------------| | 1000 | | 0 | RECURRENT INCOME | Header | | | TRUE | FALSE | | RECURRENT INCOME | | | 1020 | 1000 | 1 | Fees and Charges | Header | 1020 | | FALSE | FALSE | | Fees and Charges | | | | 1020 | 2 | 0110 - Gross Fees - Students (Excl FFPOS) | MainAccount | | | FALSE | FALSE | 0110 | 0110 - Gross Fees - Students (Excl FFPOS) | | | | 1020 | 2 | 0200 - Gross Fees - FFPOS | MainAccount | | | FALSE | FALSE | 0200 | 0200 - Gross Fees - FFPOS | | | | 1020 | 1 | Total Fees and Charges | | 1020 | | TRUE | FALSE | | Total Fees and Charges | | | 1060 | 1000 | 1 | Other Student Charges | Header | 1060 | | FALSE | FALSE | | Other Student Charges | | | | 1060 | 2 | 0150 - Compulsory Charges | MainAccount | | | FALSE | FALSE | 0150 | 0150 - Compulsory Charges | | | | 1060 | 2 | 0160 - Other Income from Students | MainAccount | | | FALSE | FALSE | 0160 | 0160 - Other Income from Students | | | | 1060 | 2 | 0170 - Income from Excursions/Trips | MainAccount | | | FALSE | FALSE | 0170 | 0170 - Income from Excursions/Trips | | | | 1060 | 1 | Total Other Student Charges | | 1060 | | TRUE | FALSE | | Total Other Student Charges | | | | 1000 | 0 | TOTAL RECURRENT INCOME | Subtotal | 1000 | | TRUE | FALSE | | TOTAL RECURRENT INCOME | | | | ... | ... | ... | ... | ... | ... | ... | ... | | ... | | | | 1000-2000 | 0 | SURPLUS / (DEFICIT) FROM RECURRENT ACTIVITIES | Subtotal | 1000 | 2000 | TRUE | FALSE | | SURPLUS / (DEFICIT) FROM RECURRENT ACTIVITIES | | | | ... | ... | ... | ... | ... | ... | ... | ... | | ... | | | | 1000-3000-4000 | 0 | Surplus (Deficit) Before Depreciation/Amortisation | Subtotal | 1000,3010,4010 | 2000,3040,4200 | TRUE | FALSE | | Surplus (Deficit) Before Depreciation/Amortisation | |
The order of the lines is as is, on the page. There is no other "sort order" column. For the purposes of the explanation below, the above table will be called PnlMap.
Columns
| Column name | Data type | Description |
|---------------|-----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| RowID | string | Must be unique, and must exist if it is referred to in other rows. This column does not have to be fully populated (it will be filled in Power BI). |
| ParentRowID | string | Refers to the header row ID under which this row will be summed under (summing is recursive, i.e. will sum all of the children). |
| Label | string | The text label (i.e. the report row label, or the main account name) |
| IndentLevel | number | Specifies the number of leading spaces to add to the label. (Can be excluded from Power BI) |
| LabelDisplay | string | Labels, with indentation applied. `=REPT(" ",[@IndentLevel])&[@Label]` |
| RowType | string | Explanation in the next section |
| SumAdd | string | Reference to a header row. All of its children rows will be summed. Can be a comma-separated string for multiple header rows (use =TEXTJOIN(",", , [cell references])). |
| SumSubtract | string | Reference to a header row. All of its children rows will be summed, _and then subtracted from the total_. Can be a comma-separated string for multiple header rows. |
| IsBold | boolean | For formatting inside of Power BI |
| IsItalic | boolean | For formatting inside of Power BI |
| MainAccountID | string | Must be string to preserve leading zeroes. |
| SignOverride | number | Values: 1, -1. This will force the value to be the sign specified. |
Row Types
| RowType | Description | |----------------|--------------------------------------------------------------------------------------------------------------| | Header | A header in the report (of any kind, of any level) | | MainAccount | A main account. General ledger transactions will be summed for this main account. | | Subtotal | A predefined subtotal. Will take header row references from SumAdd and SumSubtract and total them together. | | Spacer | A blank line. | | NetProfit | Only used in Balance Sheet Report (because it is not a GL main account) | | (Blank) | If it is left blank, it will not appear in Power BI |
Power Query
For each type of report, there are two tables to create:
- Mapping file
- Leaves file
Mapping file
This pulls in the mapping file almost as-is. It adds a sort column to enforce the row order, and also fills out the row IDs.
Power Query: `mapping`
let
// table = pull in the table from Excel
// add an index so that all of the rows and header can be sorted in Power BI
#"Added Index" = Table.AddIndexColumn(table, "SortOrder", 0, 1, Int64.Type),
#"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"SumAdd", type text}, {"SumSubtract", type text}, {"RowID", type text}, {"ParentRowID", type text}}),
// fill out the blank values in [RowID] - every row needs an ID
#"Fill MainAccount RowID" = Table.FromRecords(
List.Transform(
Table.ToRecords(#"Changed Type"),
each _ & [
RowID = if ([RowID] = null or [RowID] = "") and [RowType] = "MainAccount" then "MA_" & [MainAccountID]
else if [RowType] = "Subtotal" then "SB_" & [ParentRowID]
else [RowID]
]
),
Value.Type(#"Changed Type")
)
in
#"Fill MainAccount RowID"
Leaves file
This is a direct, flat table that correlates every main account to every subtotal that it participates in.
The table format looks like this:
| RowID | DescendantRowID | Sign | |-------|-----------------|------| | 1020 | MA_0200 | 1 | | 1020 | MA_0110 | 1 | | 1060 | MA_0170 | 1 | | 1060 | MA_0160 | 1 | | 1060 | MA_0150 | 1 |
Why is it called "leaves"? I dunno, the AI picked that name.
Power Query: `leaves`
// Generated by Claude Sonnet 4.6 (claude-sonnet-4.6) | 2026-06-29
let
Source = mapping,
Buffered = Table.Buffer(
Table.SelectColumns(Source, {"RowID", "ParentRowID", "RowType", "SumAdd", "SumSubtract"})
),
Structural = Table.SelectColumns(Buffered, {"RowID", "ParentRowID", "RowType"}),
#"Filtered Rows" = Table.SelectRows(Structural, each ([RowType] <> null)),
// ── PART 1: Ancestry bridge (Header → MainAccount leaves) ────────────────
ChildIndex = let
WithParent = Table.SelectRows(
#"Filtered Rows",
each [ParentRowID] <> null and [ParentRowID] <> ""
),
Grouped = Table.Group(
WithParent,
{"ParentRowID"}, // pick up all of the parent IDs
{{"Children", each [RowID], type list}} // list out all of their children
),
AsRecord = Record.FromList(
Grouped[Children],
List.Transform(Grouped[ParentRowID], each Text.From(_))
)
in
AsRecord,
getChildren = (targets as list) as list =>
List.Combine(
List.Transform(
targets,
(id) =>
let key = Text.From(id)
in if Record.HasFields(ChildIndex, key)
then Record.Field(ChildIndex, key)
else {}
)
),
getAllDescendants = (ancestorID as text) as list =>
let
chain = List.Generate(
() => {ancestorID},
each List.Count(_) <> 0,
each getChildren(_)
)
in
List.Combine(chain),
HeaderIDs = Table.SelectRows(#"Filtered Rows", each [RowType] = "Header")[RowID],
MainAccountIDs = List.Buffer(Table.SelectRows(#"Filtered Rows", each [RowType] = "MainAccount")[RowID]),
AncestryRecords = List.Combine(
List.Transform(
HeaderIDs,
(ancestorID) =>
List.Transform(
List.Select(getAllDescendants(ancestorID), each List.Contains(MainAccountIDs, _)),
(descendantID) => [AncestorRowID = ancestorID, DescendantRowID = descendantID]
)
)
),
Ancestry = Table.FromRecords(
AncestryRecords,
type table [AncestorRowID = text, DescendantRowID = text]
),
// ── PART 2: CalcRef expansion (Subtotal rows → AncestorRowID + Sign) ─────
SubtotalRows = Table.SelectColumns(
Table.SelectRows(Buffered, each [RowType] = "Subtotal" or [RowType] = "Header"),
{"RowID", "SumAdd", "SumSubtract"}
),
splitAndSign = (text as nullable text, sign as number) as list =>
let
cleaned = if text = null then "" else Text.Trim(text),
arr = if cleaned = "" then {} else List.Transform(Text.Split(cleaned, ","), each Text.Trim(_)),
records = List.Transform(arr, each [AncestorRowID = _, Sign = sign])
in
records,
WithLists = Table.TransformColumns(
SubtotalRows,
{
{"SumAdd", each splitAndSign(_, 1)},
{"SumSubtract", each splitAndSign(_, -1)}
}
),
Stacked = Table.Combine({
Table.RenameColumns(Table.SelectColumns(WithLists, {"RowID", "SumAdd"}), {"SumAdd", "Refs"}),
Table.RenameColumns(Table.SelectColumns(WithLists, {"RowID", "SumSubtract"}), {"SumSubtract", "Refs"})
}),
Expanded = Table.ExpandListColumn(Stacked, "Refs"),
NoNulls = Table.SelectRows(Expanded, each [Refs] <> null),
GroupSums = Table.TransformColumnTypes(
Table.ExpandRecordColumn(NoNulls, "Refs", {"AncestorRowID", "Sign"}, {"AncestorRowID", "Sign"}),
{{"AncestorRowID", type text}, {"Sign", Int64.Type}}
),
// ── PART 3: Join → flat (RowID, DescendantRowID, Sign) ───────────────────
Joined = Table.Join(
GroupSums, "AncestorRowID",
Ancestry, "AncestorRowID",
JoinKind.Inner
),
Result = Table.TransformColumnTypes(
Table.SelectColumns(Joined, {"RowID", "DescendantRowID", "Sign"}),
{{"RowID", type text}, {"DescendantRowID", type text}, {"Sign", Int64.Type}}
)
in
Result
Table relationships
I'm assuming that we already have a main accounts table ready in the data model (let's call it mainaccounts).
The P&L and the Balance Sheet each have their own mapping file, so the Power Query above is run once per statement. This gives two pairs of tables: pnlmapping + pnlleaves, and bsmapping + bsleaves. The DAX below uses those names.
Each mapping table only connects to mainaccounts. The leaves tables do not connect to anything.
mainaccounts must also be connected to generalledger, naturally, as this is where all of the numbers will come from. I'll also put in the calendar table for completeness, because time is an essential part of these reports.
pnlmapping[MainAccountID] (*)-->(*) mainaccounts[MainAccountID] // this is many-to-many because there are blanks in pnlmapping[MainAccountID]
bsmapping[MainAccountID] (*)-->(*) mainaccounts[MainAccountID] // same for the balance sheet
mainaccounts[MainAccountID] (1)-->(*) generalledger[MainAccountID]
calendar[Date] (1)-->(*) generalledger[Date]
mainaccounts also needs a column called [FinancialStatement] with values "PnL" or "BS".
Measures
Main Account Amount (Actual)
This is where we actually get numbers. Each individual main account in the report format will gather all of the transactions in the general ledger and represent them on the line item.
The P&L and the Balance Sheet need different logic here. The P&L takes the selected period and ignores the opening balance entries (Period 0). The Balance Sheet accumulates from 1 January to the selected date and keeps Period 0, because opening balances are part of the position.
DAX: `PnL Main Account Amount Actual`
PnL Main Account Amount Actual = SUMX(
mainaccounts,
[Multiplier] * SUMX(
CALCULATETABLE(
RELATEDTABLE(generalledger),
generalledger[PostingLayerName] = "Current",
generalledger[PeriodName] <> "Period 0"
),
[Amount]
)
)
DAX: `BS Main Account Amount Actual`
BS Main Account Amount Actual =
VAR maxDate = MAX('calendar'[Date])
VAR minDate = DATE(YEAR(maxDate), 1, 1)
VAR acctID = SELECTEDVALUE(bsmapping[MainAccountID])
RETURN
CALCULATE(
SUMX(
FILTER(mainaccounts, mainaccounts[MainAccountID] = acctID),
[Multiplier] * SUMX(
CALCULATETABLE(
RELATEDTABLE(generalledger),
generalledger[PostingLayerName] = "Current"
),
generalledger[Amount]
)
),
FILTER(ALL('calendar'), 'calendar'[Date] >= minDate && 'calendar'[Date] <= maxDate)
)
Subtotal Actual
These measures calculate subtotals, based on the hierarchy defined in the leaves tables.
DAX: `PnL Subtotal Actual`
PnL Subtotal Actual =
VAR currentRowID = SELECTEDVALUE(pnlmapping[RowID])
VAR posLeaves = CALCULATETABLE(
SELECTCOLUMNS(pnlleaves, "ID", pnlleaves[DescendantRowID]),
REMOVEFILTERS(pnlmapping),
pnlleaves[RowID] = currentRowID,
pnlleaves[Sign] = 1
)
VAR negLeaves = CALCULATETABLE(
SELECTCOLUMNS(pnlleaves, "ID", pnlleaves[DescendantRowID]),
REMOVEFILTERS(pnlmapping),
pnlleaves[RowID] = currentRowID,
pnlleaves[Sign] = -1
)
VAR pos = CALCULATE(
[PnL Main Account Amount Actual],
REMOVEFILTERS(pnlmapping),
TREATAS(posLeaves, pnlmapping[RowID])
)
VAR neg = CALCULATE(
[PnL Main Account Amount Actual],
REMOVEFILTERS(pnlmapping),
TREATAS(negLeaves, pnlmapping[RowID])
)
RETURN IF(
ISEMPTY(posLeaves) && ISEMPTY(negLeaves),
BLANK(),
pos - neg
)
DAX: `BS Subtotal Actual`
BS Subtotal Actual =
VAR maxDate = MAX('calendar'[Date])
VAR minDate = DATE(YEAR(maxDate), 1, 1)
VAR rowID = SELECTEDVALUE(bsmapping[RowID])
VAR posLeafRowIDs =
CALCULATETABLE(
SELECTCOLUMNS(bsleaves, "ID", bsleaves[DescendantRowID]),
REMOVEFILTERS(bsmapping),
REMOVEFILTERS(bsleaves),
bsleaves[RowID] = rowID,
bsleaves[Sign] = 1
)
VAR posAccounts =
CALCULATETABLE(
SELECTCOLUMNS(bsmapping, "MA", bsmapping[MainAccountID]),
REMOVEFILTERS(bsmapping),
TREATAS(posLeafRowIDs, bsmapping[RowID]),
NOT ISBLANK(bsmapping[MainAccountID])
)
VAR negLeafRowIDs =
CALCULATETABLE(
SELECTCOLUMNS(bsleaves, "ID", bsleaves[DescendantRowID]),
REMOVEFILTERS(bsmapping),
REMOVEFILTERS(bsleaves),
bsleaves[RowID] = rowID,
bsleaves[Sign] = -1
)
VAR negAccounts =
CALCULATETABLE(
SELECTCOLUMNS(bsmapping, "MA", bsmapping[MainAccountID]),
REMOVEFILTERS(bsmapping),
TREATAS(negLeafRowIDs, bsmapping[RowID]),
NOT ISBLANK(bsmapping[MainAccountID])
)
VAR pos = IF(
NOT ISEMPTY(posAccounts),
CALCULATE(
SUMX(
mainaccounts,
[Multiplier] * SUMX(
CALCULATETABLE(
RELATEDTABLE(generalledger),
generalledger[PostingLayerName] = "Current"
),
generalledger[Amount]
)
),
TREATAS(posAccounts, mainaccounts[MainAccountID]),
FILTER(ALL('calendar'), 'calendar'[Date] >= minDate && 'calendar'[Date] <= maxDate)
),
0
)
VAR neg = IF(
NOT ISEMPTY(negAccounts),
CALCULATE(
SUMX(
mainaccounts,
[Multiplier] * SUMX(
CALCULATETABLE(
RELATEDTABLE(generalledger),
generalledger[PostingLayerName] = "Current"
),
generalledger[Amount]
)
),
TREATAS(negAccounts, mainaccounts[MainAccountID]),
FILTER(ALL('calendar'), 'calendar'[Date] >= minDate && 'calendar'[Date] <= maxDate)
),
0
)
RETURN IF(
ISEMPTY(posAccounts) && ISEMPTY(negAccounts),
BLANK(),
pos - neg
)
Balance Sheet measures
These two measures are balance-sheet-specific, and they are what make the Balance Sheet dispatcher different from the P&L one.
DAX: `BS Net Profit`
BS Net Profit =
VAR maxDate = MAX('calendar'[Date])
VAR minDate = DATE(YEAR(maxDate), 1, 1)
RETURN
CALCULATE(
-SUMX(
FILTER(mainaccounts, mainaccounts[FinancialStatement] = "PnL"),
SUMX(
CALCULATETABLE(
RELATEDTABLE(generalledger),
generalledger[PostingLayerName] = "Current"
),
generalledger[Amount]
)
),
FILTER(
ALL('calendar'),
'calendar'[Date] >= minDate &&
'calendar'[Date] <= maxDate
)
)
DAX: `BS Total Equity`
BS Total Equity = [BS Subtotal Actual] + [BS Net Profit]
Dispatchers
Each dispatcher is the measure that goes in the field well of the table visual in Power BI. It checks what the RowType is in the row context, and selects the right measure for the situation.
This is the real win. Each statement is driven by a single measure that relies on only two others (four for the balance sheet), and there is no need to define specific measures with main account codes hardcoded in. The hierarchy has already been defined in the mapping file and the measures traverse the structure already set there to calculate the values.
Only exceptional cases like Net Profit and Total Equity need special handling, because their calculation is not a simple sum of the mapping structure (the net profit needs to reach into the P&L).
P&L
PnL Report Line Value Actual =
VAR rowType = SELECTEDVALUE(pnlmapping[RowType])
RETURN
SWITCH(
TRUE(),
rowType = "Spacer", BLANK(), // visual spacers
rowType = "MainAccount", [PnL Main Account Amount Actual], // main accounts grouped under a heading
rowType IN {"Header", "Subtotal"}, [PnL Subtotal Actual], // adding and subtracting groups against each other
BLANK()
)
Balance Sheet
BS Report Line Value Actual =
VAR rowType = SELECTEDVALUE(bsmapping[RowType])
VAR label = SELECTEDVALUE(bsmapping[Label])
RETURN
SWITCH(
TRUE(),
rowType = "Spacer", BLANK(), // visual spacers
rowType = "MainAccount", [BS Main Account Amount Actual],
rowType = "NetProfit", [BS Net Profit],
label = "TOTAL EQUITY", [BS Total Equity], // hardcoded check on the report label; must come before the Header/Subtotal catch-all
rowType IN {"Header", "Subtotal"}, [BS Subtotal Actual],
BLANK()
)