How to Build a Profit & Loss Statement in Power BI (with Subtotals That Actually Work)

Every finance team eventually asks for the same thing: "Can we see the P&L in Power BI, exactly like the one in Excel?"

The first attempt is usually a matrix with the chart of accounts on rows. It works until someone asks for Gross Profit, EBITDA or Net Income between the account groups. Those are not accounts. They don't exist in the general ledger, so a plain matrix has nowhere to put them.

This article shows the pattern we use on client projects. It needs one small layout table and one main DAX measure, and it doesn't require calculation groups or a separate measure per line.

The idea: separate the layout from the data

A P&L is a report layout sitting on top of the ledger. So we model it that way:

• GL (fact): one row per posting, with AccountKey, Date, Amount.
• Account (dimension): the chart of accounts, plus one extra column, P&L Line, that maps each account to a line of the report.
• P&L Layout (disconnected table): one row per line you want to see, in order, including the subtotals.

Here is a minimal layout table:

Sort	Line	Type
10	Revenue	Account
20	Cost of Goods Sold	Account
30	Gross Profit	Subtotal
35	Gross Margin %	Ratio
40	Salaries	Account
50	Rent & Utilities	Account
60	Marketing	Account
70	EBITDA	Subtotal
80	Depreciation & Amortization	Account
90	Interest	Account
100	Taxes	Account
110	Net Income	Subtotal
115	Net Margin %	Ratio

Keep it in Excel, SharePoint or a SQL table. Finance can then add or reorder lines without touching the model. Sort Line by Sort in Power BI.

Step 1 — a signed base measure

Ledgers usually store revenue as credits (negative) and expenses as debits (positive). For a P&L you want revenue positive and costs negative, so a subtotal is a simple sum:

GL Amount = - SUM ( GL[Amount] )

If your source already stores revenue as positive, flip the sign on the expense accounts instead (a Sign column on Account works well).

Step 2 — one measure for accounts and subtotals

The trick is that a subtotal equals the sum of every account line above it. That holds for a classic P&L: Gross Profit is everything above it, EBITDA is everything above it, and so on down to Net Income.

P&L Value =
VAR _sort = SELECTEDVALUE ( 'P&L Layout'[Sort] )
VAR _type = SELECTEDVALUE ( 'P&L Layout'[Type] )
VAR _from = IF ( _type = "Account", _sort, 0 )
VAR _lines =
    CALCULATETABLE (
        VALUES ( 'P&L Layout'[Line] ),
        ALL ( 'P&L Layout' ),
        'P&L Layout'[Type] = "Account",
        'P&L Layout'[Sort] >= _from,
        'P&L Layout'[Sort] <= _sort
    )
RETURN
    IF (
        _type IN { "Account", "Subtotal" },
        CALCULATE ( [GL Amount], TREATAS ( _lines, 'Account'[P&L Line] ) )
    )

What happens row by row:

• On an Account row, _from and _sort are the same, so _lines holds just that line. TREATAS pushes it onto Account[P&L Line] and the ledger is filtered to those accounts.
• On a Subtotal row, _from is 0, so _lines holds every account line above the subtotal. That gives the running total.

Because the layout table is disconnected, the report filters for dates, companies and departments keep working normally.

Step 3 — margin rows

A ratio row needs two things that don't depend on the current row: the denominator (Revenue) and its numerator (the subtotal it refers to). Add a Base Line column to the layout table: Gross Profit for Gross Margin %, Net Income for Net Margin %, blank elsewhere.

P&L Revenue =
CALCULATE ( [P&L Value], ALL ( 'P&L Layout' ), 'P&L Layout'[Line] = "Revenue" )

Then a display measure that switches on the row type:

P&L Display =
VAR _type = SELECTEDVALUE ( 'P&L Layout'[Type] )
VAR _base = SELECTEDVALUE ( 'P&L Layout'[Base Line] )
RETURN
    IF (
        _type = "Ratio",
        DIVIDE (
            CALCULATE ( [P&L Value], ALL ( 'P&L Layout' ), 'P&L Layout'[Line] = _base ),
            [P&L Revenue]
        ),
        [P&L Value]
    )

Use dynamic format strings on P&L Display so ratios show as 0.0% and everything else as currency:

IF ( SELECTEDVALUE ( 'P&L Layout'[Type] ) = "Ratio", "0.0%", "#,0;(#,0)" )

Step 4 — make it look like a P&L

Put 'P&L Layout'[Line] on rows, Date[Month] (or Actual / Budget / Variance) on columns, and P&L Display in values. Then:

• Turn off row subtotals. Your subtotals are now real rows.
• Highlight subtotal rows with conditional formatting on the background. Create a small measure that returns a color for Subtotal rows and blank for the rest, and apply it with Format style: Field value.
• Hide empty lines by keeping the measure blank (not zero) where there is no data. The code above already does that.

Why this pattern holds up in production

• Finance owns the layout. Adding a line like "Other Income" is a new row in the layout table plus a mapping on the accounts. No DAX changes.
• One measure to test. Budget and prior year reuse the same logic: swap [GL Amount] for [Budget Amount] in a copy of the measure, or use a calculation group on top.
• Plays well with row-level security and with any slicer, because nothing in the layout is connected to the facts.

Common pitfalls

1. Accounts with no P&L Line. They silently disappear. Add a card that sums [GL Amount] for accounts where the mapping is blank. It should always be zero.
2. Sign confusion. Decide once (revenue positive, costs negative) and apply it in the base measure only.
3. Subtotals that are not cumulative (for example, an "Operating Expenses" total that only covers a block of lines). Add a From Sort column to the layout and use it instead of 0 for that row.

---

Building a P&L, balance sheet or cash flow in Power BI for your finance team? This is our day job — see how we work on our Power BI consulting page.