Finance Dashboard – Retail

finance analytics dashboard performed in powerbi

About the Project

The data used here is from a publicly available data set for a fictitious retail company.
The dashboard depicts selected kpi’s from the organisation profit and loss accounts.In addition to this there is scenario analysis available by the means of a “what if”.

Live - Financial Performance Dashboard in PowerBi

How this dashboard is built

Most finance dashboards report the past. This one also lets you change it. Alongside the revenue, margin and income statement pages sits a financial simulator driven by what-if parameters, so a user can move price, quantity, variable cost and fixed cost and watch the P&L respond. That single feature changes what the report is for: it stops being a record and becomes a planning tool.

Four working pages plus three tooltip pages: Revenue & Margin, Income Statement, Financial Simulator, and an Orders Details table for drill-through.

The data model

Table	Grain and role
fRevenue	Transactional fact: order number, order date, due date, quantity, product, salesperson and team.
Income Statement	The statement layout table, separate from the transactions so the P&L can be structured independently of how orders are recorded.
dAccount / dAccountHeader	Detailed account, subheader and header account. Three levels, which is what lets the statement roll up correctly rather than being hand-grouped in a visual.
dProduct	Product, category, group and supplier.
dDate	Marked date table driving month comparisons.
WIFQty, WIFUnitPrice, WIFVariableCosts, WIFExpenses	What-if parameter tables. Disconnected by design: they feed the simulator without filtering the actuals.

The what-if tables are the interesting design decision. They are deliberately not related to the fact table. If they were, moving a slider would filter the data instead of recalculating against it. Keeping them disconnected and reading their value with SELECTEDVALUE is what makes a simulator work.

Column names below are taken from the model. These are the measure definitions as the model computes them, not a paste of the formula bar.

The measures

The P&L spine

Revenue = SUM( fRevenue[Amount] )

Costs =
CALCULATE( [PnL], dAccountHeader[Header Account] = "Costs" )

Expenses =
CALCULATE( [PnL], dAccountHeader[Header Account] = "Expenses" )

Gross Margin = [Revenue] - [Costs]

% MC = DIVIDE( [Gross Margin], [Revenue] )

Operating Income = [Gross Margin] - [Expenses]

Each line is defined from the account hierarchy rather than hard-coded, so adding an account to the chart of accounts flows through to the statement without touching a measure.

Statement analysis

Vertical analysis expresses every line as a share of revenue; horizontal analysis compares it to the prior period. Both are what turn a statement into a comparison.

VA = DIVIDE( [PnL], CALCULATE( [Revenue], REMOVEFILTERS( dAccount ) ) )

HA =
VAR Previous = CALCULATE( [PnL], DATEADD( dDate[Date], -1, MONTH ) )
RETURN DIVIDE( [PnL] - Previous, ABS( Previous ) )

Month-on-month movement

OR Month Increment U$ =
[Revenue] - CALCULATE( [Revenue], DATEADD( dDate[Date], -1, MONTH ) )

GM Month Increment % =
VAR Previous = CALCULATE( [Gross Margin], DATEADD( dDate[Date], -1, MONTH ) )
RETURN DIVIDE( [Gross Margin] - Previous, Previous )

Concentration

Repeated for revenue, gross margin and margin percentage: which product group dominates, and by how much.

OR Top 1 Product Group Name =
CONCATENATEX(
    TOPN( 1, VALUES( dProduct[Group] ), [Revenue], DESC ),
    dProduct[Group]
)

OR Top 1 Product Group % =
DIVIDE(
    CALCULATE( [Revenue], KEEPFILTERS( TOPN( 1, VALUES( dProduct[Group] ), [Revenue], DESC ) ) ),
    CALCULATE( [Revenue], REMOVEFILTERS( dProduct ) )
)

The simulator

This is the part worth studying. The what-if values are read from the disconnected parameter tables, applied to the actuals, and the result compared back to the unmodified P&L.

WIFQty Value       = SELECTEDVALUE( WIFQty[Qty %], 0 )
WIFUnitPrice Value = SELECTEDVALUE( WIFUnitPrice[Price %], 0 )

PnL WIF =
VAR NewRevenue =
    [Revenue] * ( 1 + [WIFQty Value] ) * ( 1 + [WIFUnitPrice Value] )
VAR NewCosts =
    [Costs] * ( 1 + [WIFQty Value] ) * ( 1 + [WIFVariableCosts Value] )
VAR NewExpenses =
    [Expenses] * ( 1 + [WIFFixedCosts Value] )
RETURN NewRevenue - NewCosts - NewExpenses

VAR Income   = [PnL WIF] - [Operating Income]
VAR Income % = DIVIDE( [VAR Income], ABS( [Operating Income] ) )

VAR Income KPI =
SWITCH( TRUE(), [VAR Income] > 0, 1, [VAR Income] < 0, -1, 0 )

Note that quantity affects both revenue and variable costs, while fixed costs move independently. Getting that relationship right is the difference between a simulator and a spreadsheet that only pretends to model the business.

Walking the pages

Revenue & Margin

Operational revenue, gross margin and margin percentage as headline cards, each with a tooltip page behind it giving the product breakdown on hover. Below sit a decomposition tree and a key influencers visual, which together answer "why did this move" without the user having to guess which dimension to check first. The decomposition tree in particular is doing analysis, not just display: it finds the largest contributor at each level rather than waiting to be told where to look.

Income Statement

The formal statement laid out by the account hierarchy, with a waterfall chart showing the path from revenue down to operating income. The waterfall is the right visual here because it makes the size of each deduction visible in a way a table of numbers does not.

Financial Simulator

Four parameter slicers for quantity, unit price, variable costs and fixed costs, with the simulated P&L beside the actual one and the variance measures between them. A user can ask "what happens to operating income if we lift price three percent and volume drops five" and see the answer immediately.

Orders Details

A flat table for drill-through, so any number on any page can be traced back to the underlying orders. Every finance report needs this page, because the first question after a surprising number is always "show me the transactions".

Build decisions worth calling out

What-if tables stay disconnected. They feed calculations without filtering the actuals, which is the whole basis of the simulator working.
Account hierarchy drives the statement. Costs and expenses are selected by header account, so the chart of accounts can change without rewriting measures.
Quantity flows to both revenue and variable cost. The model respects that selling more units costs more to fulfil, which is where naive simulators fall down.
Tooltip pages for the headline cards. Breakdown on hover instead of three more visuals competing for space.
Decomposition tree and key influencers. Analytical visuals that suggest where to look, rather than requiring the user to already know.
A drill-through details page. Every aggregate is traceable to its transactions.

What I would change next

The simulator applies uniform percentage changes across the whole business, which is a fair first approximation but not how price changes actually work. Making the parameters apply per product group, so a price rise can be modelled on one category while another holds, would make the output far more credible. I would also add a saved-scenario table, so a user can compare two or three named scenarios side by side rather than only against actuals, and a sensitivity view showing which of the four levers moves operating income most for a given percentage change.