Skip to content
Tutorials Center
My learning

Topics

Lesson 49 of 56 ยท Projects

Project: Profit and Loss Report

Build a profit and loss report in Power BI: an account hierarchy matrix, variance to budget, a waterfall bridge and DAX measures, checked with pandas.

A profit and loss report (P&L) is the report finance teams open first every month. It answers three questions: what did we earn, what did it cost, and how does that compare with the plan? It looks like a simple table, but it is one of the best exercises for Power BI because it forces you to get four things right: a sign convention, an account hierarchy, subtotals that add up, and a variance that always points in the same direction.

In this project you will model a small ledger, write the measures, lay out a matrix with revenue and cost groups, add a waterfall that bridges budget to actual, and prove every number with pandas. Power BI Desktop is a GUI tool, so the model and report steps are described in text and the DAX is read-only. The runnable examples are Python stand-ins that compute the same figures from the same data, so you can see what the measures must return before you build them.

The plan

Build it in five steps

Load A ledger fact and an account table.
Model One account to many ledger rows.
Measure Actual, Budget, Variance.
Report Matrix, waterfall, slicers.

Finish by checking totals against a second tool.

1 / 2

The data and the model

Imagine a company with seven accounts in three groups: Revenue (product sales, services), Cost of sales (materials, labour) and Operating costs (salaries, marketing, rent). The ledger has one row per account per month with an Actual and a Budget column.

The model follows the star schema from Star Schema: Facts and Dimensions:

  • Ledger (fact): Account, Month, Actual, Budget. The grain is one account in one month.
  • Accounts (dimension): Account, Group, Group Order. One row per account.
  • A Date table related on the month, built as shown in Date Tables and Time Intelligence Setup (needed later for year-to-date).

Relate Accounts[Account] to Ledger[Account] as one-to-many with a single filter direction from the dimension to the fact (see Relationships, Cardinality and Filter Direction). In Model view, hide the raw Actual and Budget columns, so report authors use measures instead of dragging a column and getting an implicit sum.

The sign convention

Choose one rule before you write any DAX: store revenue as positive and costs as negative. Then net profit is simply the sum of every row, a subtotal is the sum of its rows, and Actual - Budget is favourable when positive for revenue and for costs alike. Spending 55 against a budget of 45 gives -55 - (-45) = -10, an unfavourable result, with no special case for cost lines.

If your source system stores costs as positive numbers, flip them in Power Query (multiply by -1 where the account group is a cost group) so the model, not each measure, carries the convention. See Shaping Columns and Rows for conditional columns.

Measures

Create a dedicated measure table or put these on Ledger:

DAX
Actual = SUM ( Ledger[Actual] )

Budget = SUM ( Ledger[Budget] )

Variance = [Actual] - [Budget]

Variance % = DIVIDE ( [Variance], ABS ( [Budget] ) )

ABS matters in the percentage. A cost line has a negative budget, and dividing by a negative number would flip the sign of the percentage, which breaks the "positive is good" rule. DIVIDE returns blank instead of an error when the budget is zero (see Aggregators and Iterators).

Subtotals that are not part of the hierarchy use CALCULATE with a filter on the group (see CALCULATE and Filter Context):

DAX
Gross Profit =
CALCULATE (
    [Actual],
    Accounts[Group] IN { "Revenue", "Cost of sales" }
)

Gross Margin % = DIVIDE ( [Gross Profit], CALCULATE ( [Actual], Accounts[Group] = "Revenue" ) )

For year-to-date figures, rely on the date table:

DAX
Actual YTD = TOTALYTD ( [Actual], 'Date'[Date] )

You will find the details in Time Intelligence.

The matrix

Add a Matrix visual. Put Accounts[Group] and then Accounts[Account] in Rows, the month in Columns or leave columns empty, and the measures Actual, Budget, Variance and Variance % in Values. Switch the values to display on rows if the report should read like a statement.

Three details make the matrix behave like a P&L:

  1. Hierarchy. With two fields in Rows, the matrix shows the group as an expandable row with subtotals. Use the expand buttons or turn on "Show on rows" in the Row headers settings. The subtotal of a group is the measure evaluated with the group filter in place, which is why it always equals the sum of its accounts.
  2. Order. Groups sort alphabetically by default, which puts "Cost of sales" first. Add Group Order (1, 2, 3) and use Sort by column on Group so Revenue comes first.
  3. Grand total. Because costs are negative, the matrix grand total of Actual is net profit. No extra measure is needed.

Apply conditional formatting to the Variance column as in Conditional Formatting, KPIs and Cards: green when above zero, red below, grey for blank. Because of the sign convention, one rule serves every row.

Prove the numbers first

Before you trust the matrix, calculate what it must show. The stand-in below builds the ledger, joins the accounts, and produces the same group subtotals, net profit and variances.

Python Stand-in: P&L by group with variance

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Compare this with the matrix: the same four group rows, the same net profit, and the same percentages. If the matrix grand total disagrees with the net profit here, the usual suspects are a missing relationship, a hidden filter on a slicer or a cost stored with the wrong sign.

Drilling to accounts

The next stand-in shows the matrix with the full hierarchy: each account under its group, with a subtotal per group, and then verifies the rule that makes subtotals trustworthy: the sum of the accounts equals the group, and the sum of the groups equals net profit.

Python Stand-in: account hierarchy and subtotals

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In the report, you can add a bookmark or a drill-through page for an account so that a click on a variance opens the monthly detail (see Drill-through, Tooltips, Bookmarks and Buttons).

The waterfall: from budget to actual

A waterfall visual explains a variance better than a table: it starts at budget net profit, adds or subtracts each group's variance, and lands at actual net profit. The visual needs a category field and a value, so you build a small bridge table with the steps and one measure.

DAX
Bridge =
DATATABLE (
    "Step", STRING,
    "Order", INTEGER,
    {
        { "Budget net profit", 1 },
        { "Revenue", 2 },
        { "Cost of sales", 3 },
        { "Operating costs", 4 },
        { "Actual net profit", 5 }
    }
)

Bridge Value =
VAR _step = SELECTEDVALUE ( Bridge[Step] )
RETURN
    SWITCH (
        _step,
        "Budget net profit", [Budget],
        "Actual net profit", [Actual],
        CALCULATE ( [Variance], Accounts[Group] = _step )
    )

The Bridge table has no relationship to the model: it only supplies category names. The SWITCH returns the budget for the first step, the actual for the last and a group's variance for the steps in between. Put Bridge[Step] on the Category axis, sorted by Order, and Bridge Value on Y axis. The first and last bars must be shown as totals, not as changes; recent versions of the visual let you mark a bar as a total in the format options, so check the Format pane in your version.

The test of a waterfall is that it closes: budget plus the three variances must equal actual.

Python Stand-in: the waterfall must close

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The three variances sum to 67, and 465 plus 67 gives the actual 532. If a waterfall does not close, either a group is missing from the bridge or a measure ignores a filter.

Slicers, pages and checks

Finish the report with a month slicer (or a date range, see Filters, Slicers and Sync), a card for net profit, a card for net margin and a KPI for actual against budget. A common layout is a summary page with the cards, the waterfall and the matrix collapsed to groups, and a detail page with every account.

Keep a short checklist for every refresh:

  • The matrix grand total equals the card for net profit.
  • The waterfall closes.
  • Filtering to one month gives the same results as the matching rows of the ledger.
  • The totals agree with the finance system, whose report the business already trusts.

Print or export the matrix to compare it with the system, and keep a pandas or SQL query nearby that recomputes the totals from the raw data. When a number is disputed, the first step is always to recompute it independently.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why store costs as negative amounts in the ledger?

    With revenue positive and costs negative, subtotals and the grand total are simple sums, and one variance formula works for revenue and cost lines alike.
  2. 2. Why does the Variance % measure divide by ABS ( [Budget] )?

    Cost budgets are negative. Dividing by the absolute value keeps positive meaning favourable on every line.
  3. 3. In the waterfall bridge, how is Bridge related to the rest of the model?

    The Bridge table is disconnected. The Bridge Value measure reads the selected step with SELECTEDVALUE and calculates the right figure itself.
  4. 4. What is the simplest test that a waterfall from budget to actual is correct?

    A bridge must close: the starting total plus the changes equals the ending total. If it does not, a step is missing or a measure ignores a filter.

Summary

  • A P&L needs a sign convention first: revenue positive, costs negative, so totals are sums and variance is Actual minus Budget everywhere.
  • The model is a ledger fact table plus an account dimension with a group and a sort order; hide the raw columns and report with measures.
  • Variance % divides by ABS ( [Budget] ) and uses DIVIDE to avoid errors and sign flips.
  • A matrix with group and account in rows gives subtotals for free; sort the groups with a sort-by column.
  • A waterfall uses a disconnected bridge table and a SWITCH measure; it must close from budget to actual.
  • Recompute the totals independently (pandas or the source system) after every build or refresh.

Next, switch from money to people with Project: HR and Headcount Dashboard.