Income Statement (P&L) in Power BI with DAX

Welcome to Level 4: Expert. You've finished and published a complete sales dashboard. Now you'll build the reports finance teams and executives ask for most, connect Power BI to real databases, run advanced customer and product analysis, and prepare for the official certification. We start with the report every owner and CFO wants to see: the income statement, or P&L (profit and loss).

The technical challenge is interesting: an income statement isn't a list of categories that add up. It has lines that come from different tables (sales, cost of goods sold, expenses), subtotals that subtract, and a fixed order. A regular matrix can't do that. You'll solve it with a technique professionals use all the time: a layout table and a measure built on SWITCH.

Step 1: the expenses table

The practice file has sales and product costs, but no operating expenses. Create them with Home › Enter data (at a real company they'd come from QuickBooks, your ERP or a finance spreadsheet):

Name it Expenses, make sure Date is a Date type and Amount a Decimal Number, and relate Expenses[Date] to 'Calendar'[Date] (Lesson 8). That way expenses respond to the same date filters as sales. Add more months in the same format whenever you like.

Create the base measure:

Operating Expenses = SUM ( Expenses[Amount] )

Step 2: the P&L layout table

This table defines the report's rows, in order. Create it with Enter data too and name it P&L Lines:

Two important details:

• Select the Line column and, under Column tools › Sort by column, choose Order. Otherwise the matrix would list the lines alphabetically.
• Don't relate this table to any other. The measure will read which line is being displayed and decide what to calculate.

Step 3: the measure that calculates every line

P&L Amount =
VAR SelectedLine = SELECTEDVALUE ( 'P&L Lines'[Line] )
VAR LineType     = SELECTEDVALUE ( 'P&L Lines'[Type] )
RETURN
    SWITCH (
        TRUE (),
        SelectedLine = "Sales", [Total Sales],
        SelectedLine = "Cost of goods sold", - [Total Cost],
        SelectedLine = "Gross profit", [Gross Profit],
        LineType = "Expense", - CALCULATE ( [Operating Expenses], Expenses[Account] = SelectedLine ),
        SelectedLine = "Total operating expenses", - [Operating Expenses],
        SelectedLine = "Operating income", [Gross Profit] - [Operating Expenses]
    )

How it works:

• SELECTEDVALUE returns the line of the row the matrix is calculating (or blank if there are several, as on the total row).
• SWITCH ( TRUE (), condition, result, … ) checks the conditions in order and returns the result of the first one that's true. It's the clean way to write a stack of nested IFs.
• Costs and expenses show as negative numbers, as in a formal income statement, so reading top to bottom "adds up" on its own.
• The four expense lines share one rule: they filter Expenses to the account with the same name as the line. If you add "Marketing" tomorrow, you just add one row to each table.

And percent of sales, which is how a P&L is analyzed (the income statement lesson in our bookkeeping course explains why):

% of Sales = DIVIDE ( [P&L Amount], [Total Sales] )

Because P&L Lines isn't related to Sales, [Total Sales] doesn't change from line to line: it's always the period's total sales, exactly the denominator you need.

Step 4: the matrix

1. VisualizationsInsert a matrixThe visual that supports rows, columns and multiple values.
2. Matrix fieldsRows, columns and valuesRows: 'P&L Lines'[Line]. Columns: year and month from 'Calendar'. Values: P&L Amount and % of Sales.
3. Format › Row subtotalsTurn off subtotalsYour subtotals are already lines in the layout; the matrix's own would come out blank or duplicated.
4. Filters paneFilter to January 2025Check every line against the table below before moving on.

The business read: the distributor has a healthy 30% gross margin, but expenses swallow nearly all of it, leaving an operating margin of just 2.6%. A small dip in sales or a jump in fuel costs would push it into a loss. That's what makes a P&L in Power BI so valuable: add a region or sales rep slicer and you'll see which part of the business carries the rest.

Power BI has a native waterfall chart. If you use it with 'P&L Lines'[Line] as the category and P&L Amount as the value, filter the visual to exclude lines of type Subtotal: the waterfall calculates the running total itself, and if you leave the subtotals in, it counts them twice.

Break-even: how much you need to sell to avoid a loss

With gross margin as a percentage and fixed expenses, one measure answers the question every owner asks:

Break-even Sales =
DIVIDE ( [Operating Expenses], [Gross Margin %] )

Margin of Safety % =
DIVIDE ( [Total Sales] - [Break-even Sales], [Total Sales] )

For January 2025: $6,075 ÷ 0.30192 (the exact margin) = $20,121. Summit sold $21,989, so its margin of safety was just 8.5%: if sales had dropped a little more than 8%, the month would have closed at a loss. With this measure in a card, the owner knows every month how far above (or below) break-even the business is. It assumes gross margin % and expenses don't change with sales volume.

Step 5: financial formatting

• Number format with thousands separators and two decimals; show negatives with a minus sign or in parentheses, following your company's standard.
• Highlight subtotals: conditional formatting can't make text bold, but it can add a light background color. Create a measure Is Subtotal = IF ( SELECTEDVALUE ( 'P&L Lines'[Type] ) = "Subtotal", 1, 0 ) and use it in Background color › Field value or with rules.
• Color on the %: red when operating income is negative, with a conditional formatting rule.
• Add a vs. prior month or vs. budget column using the time intelligence techniques from Lesson 8.

New layout (Order · Line · Type):
 1 Sales · Revenue
 2 Cost of goods sold · Cost
 3 Gross profit · Subtotal
 4 Payroll · Expense
 5 Rent · Expense
 6 Delivery · Expense
 7 Other expenses · Expense
 8 EBITDA · Subtotal
 9 Depreciation · Expense
10 Total operating expenses · Subtotal
11 Operating income · Subtotal

New branch in the measure (before "Total operating expenses"):
    SelectedLine = "EBITDA",
        [Gross Profit] - CALCULATE ( [Operating Expenses], Expenses[Account] <> "Depreciation" ),

Is Subtotal = IF ( SELECTEDVALUE ( 'P&L Lines'[Type] ) = "Subtotal", 1, 0 )

Common P&L mistakes

Summary

• A P&L combines sales, costs and expenses from different tables in a fixed order with subtotals.
• The technique: an unrelated layout table sorted by an Order column, plus a SWITCH ( TRUE (), … ) measure.
• Costs and expenses are negative; percent of sales uses total sales as the denominator.
• Turn off the matrix's subtotals and highlight your own with conditional formatting.
• Before publishing, tie out a closed month to your accounting reports.
• Break-even (expenses ÷ gross margin %) tells you how much to sell to avoid a loss: in January 2025, Summit's margin of safety was only 8.5%.

Next lesson: Import vs. DirectQuery, Gateways and Scheduled Refresh in Power BI. You'll connect Power BI to real databases: storage modes, SQL queries, the gateway and scheduled refresh.