Building a Finance Dashboard in Power BI

A finance dashboard has a narrower job than most: show whether the business is profitable, where it makes and loses money, and what drives the margin. Accuracy matters more than decoration, because finance users reconcile every number against their own figures. This walkthrough builds one on Microsoft's Financial Sample, the workbook of sales and profit by market segment and country that Microsoft provides for trying Power BI (Microsoft Learn: Financial Sample). The figures below are what the finished measures should return; the sample is illustrative data, not a real company.

Step 1: Check the data before modelling it

The workbook looks tidy, but two details can quietly break a report:

import io
import urllib.request
import pandas as pd

url = "https://go.microsoft.com/fwlink/?LinkID=521962"
finance = pd.read_excel(io.BytesIO(urllib.request.urlopen(url).read()))
print(finance.shape, "|", [c for c in finance.columns if c != c.strip()], "|", finance["Discount Band"].isna().sum(), "blank bands")
print(pd.crosstab(finance["Year"], finance["Month Number"]).to_string())

(700, 16) | [' Sales'] | 53 blank bands
Month Number  1   2   3   4   5   6   7   8   9   10  11  12
Year                                                        
2013           0   0   0   0   0   0   0   0  35  70  35  35
2014          35  35  35  35  35  70  35  35  35  70  35  70

• The Sales column is named " Sales" with a leading space. If the space survives import, a measure written as Financials[Sales] will not find the column. Rename it in Power Query so every name is clean.
• A discount band called "None" can be read as missing. Some tools, pandas included, treat the text "None" as a blank by default, which here turned 53 rows into blanks. Check category values after every import.
• The periods are uneven. 2013 covers only September to December, and some months have twice as many rows as others. A chart comparing 2013 with 2014 as whole years would be meaningless; compare like periods or report 2014 alone.

With the band read correctly, every row has one of four values:

finance = pd.read_excel(io.BytesIO(urllib.request.urlopen(url).read()), keep_default_na=False)
finance.columns = finance.columns.str.strip()
print(finance["Discount Band"].value_counts().to_string())

Discount Band
High      245
Medium    242
Low       160
None       53

Step 2: Define the measures

Finance dashboards should use explicit measures, so every visual uses the same definition. With the table named Financials:

Gross Sales = SUM ( Financials[Gross Sales] )
Discounts = SUM ( Financials[Discounts] )
Sales = SUM ( Financials[Sales] )
COGS = SUM ( Financials[COGS] )
Profit = SUM ( Financials[Profit] )
Gross Margin % = DIVIDE ( [Profit], [Sales] )
Discount % = DIVIDE ( [Discounts], [Gross Sales] )

DIVIDE returns blank rather than an error when the denominator is zero (DAX reference: DIVIDE), which keeps filtered visuals clean. Ratios are always computed from totals, never averaged across rows.

Step 3: Reconcile, then build the profit bridge

Before building any visual, confirm that the numbers add up the way finance expects:

totals = finance[["Gross Sales", "Discounts", "Sales", "COGS", "Profit"]].sum() / 1e6
print((totals.round(2)).to_string())
print("gross sales - discounts = sales:", round(totals["Gross Sales"] - totals["Discounts"] - totals["Sales"], 6) == 0)
print("sales - COGS = profit:", round(totals["Sales"] - totals["COGS"] - totals["Profit"], 6) == 0)
print(f"gross margin: {totals['Profit'] / totals['Sales']:.1%}")

Gross Sales    127.93
Discounts        9.21
Sales          118.73
COGS           101.83
Profit          16.89
gross sales - discounts = sales: True
sales - COGS = profit: True
gross margin: 14.2%

Both identities hold, so the figures can be shown as a bridge: 127.93 million of gross sales, less 9.21 million of discounts, less 101.83 million of cost of goods, leaves 16.89 million of profit, a 14.2% margin. A waterfall chart is built for exactly this, showing a running total as values are added and subtracted (Microsoft Learn: waterfall charts). Finance users read a bridge faster than four separate cards.

Step 4: Find where money is made and lost

def margins(by):
    g = finance.groupby(by)[["Gross Sales", "Discounts", "Sales", "Profit"]].sum()
    return pd.DataFrame({"Sales (m)": (g["Sales"] / 1e6).round(2),
                         "Profit (m)": (g["Profit"] / 1e6).round(2),
                         "Margin": (g["Profit"] / g["Sales"]).map("{:.1%}".format),
                         "Discount %": (g["Discounts"] / g["Gross Sales"]).map("{:.1%}".format)})
print(margins("Segment").sort_values("Profit (m)", ascending=False).to_string())
print("rows with a loss:", (finance["Profit"] < 0).sum(), "| all Enterprise:", (finance.loc[finance["Profit"] < 0, "Segment"] == "Enterprise").all())

                  Sales (m)  Profit (m) Margin Discount %
Segment                                                  
Government            52.50       11.39  21.7%       6.9%
Small Business        42.43        4.14   9.8%       7.6%
Channel Partners       1.80        1.32  73.1%       7.0%
Midmarket              2.38        0.66  27.7%       7.8%
Enterprise            19.61       -0.61  -3.1%       6.9%

rows with a loss: 58 | all Enterprise: True

This is the finding the dashboard must surface. Enterprise brings 19.61 million of sales, the third-largest segment, yet loses money overall, and every loss-making row in the data belongs to it. Its discount rate is no higher than Government's, so discounts do not explain the loss; the cost of goods relative to price does. Channel Partners has the highest margin but tiny volume. A matrix of segments with the margin column shaded from red to green makes this visible at a glance; conditional formatting in Power BI shows how.

Step 5: Show what discounts do to margin

print(margins("Discount Band").loc[["None", "Low", "Medium", "High"]].to_string())

               Sales (m)  Profit (m) Margin Discount %
Discount Band                                         
None                7.94        1.74  21.9%       0.0%
Low                34.63        6.19  17.9%       2.5%
Medium             38.78        5.58  14.4%       7.2%
High               37.37        3.39   9.1%      12.5%

Margin falls at every step up in discount, from 21.9% with no discount to 9.1% with high discounts. A simple column chart of margin by discount band is one of the most useful visuals a sales and finance team can share.

Step 6: Lay out the page

• Top row: cards for Sales, Profit, Gross Margin % and Discount %; KPIs and cards in Power BI covers designing them.
• Left: the profit waterfall.
• Right: the segment matrix with margin shading, and margin by discount band below it.
• Filters: slicers for country and year, with the page stating that 2013 covers four months only.

Keep numbers in consistent units, millions here, and label every figure as sales or profit, never just "amount". Put the data source and the date of the last refresh in the page footer, so readers always know which figures they are looking at and how current they are.

Common finance dashboard mistakes

• Averaging margins. The average of five segment margins here would be 25.8%, nearly twice the true 14.2%, because Channel Partners' tiny, highly profitable sales count as much as Government's. Always divide total profit by total sales.
• Showing profit without sales. A segment's profit means little without its size; show both, and the margin.
• Mixing signs. Decide whether discounts and costs are shown as positive numbers subtracted, or as negatives, and use one convention everywhere, including the waterfall.
• Comparing uneven periods, such as four months of 2013 against twelve of 2014.
• Rounding before summing. Round only for display; totals built from rounded rows will not tie out to the ledger.
• Hiding the definitions. Add a short note or tooltip stating what sales, COGS and margin mean in this report.

Finance-specific practices

• Tie out to the source. Show a small reconciliation, such as the totals above, on a hidden or appendix page, and check it after every refresh.
• Add comparisons. Real finance reporting compares actual with budget and prior year; add a budget table and time intelligence measures when available.
• Restrict access. Finance data is often sensitive; row-level security can limit each manager to their own countries or segments.

More project ideas are in Power BI projects for your portfolio. A financial performance dashboard is one of the projects in the Power BI course.

Sources

• Download the Financial Sample Excel workbook for Power BI (Microsoft Learn) — Microsoft Learn
• Waterfall charts in Power BI (Microsoft Learn) — Microsoft Learn
• DIVIDE function (DAX reference, Microsoft Learn) — Microsoft Learn