Sales & Revenue KPIs in Power BI
A KPI is one number that tells you how the business is doing. KPI means Key Performance Indicator. In this chapter you learn 5 sales KPIs. For each one we go in the same order: Why we need it, What it means, and How to make it in Power BI.
The 5 KPIs: Total Revenue, Gross Sales, Net Sales, Average Order Value (AOV) and Sales Growth %.
The example data
Five made-up companies from five industries. Each row is one company for one year (April to March). Each company uses its own money: ₹, £, $ or €. So we never add two companies together. We read each row on its own.
- Company = company name, its industry and city.
- Cur = the currency its numbers are in.
- Orders = how many orders customers placed.
- Gross = Qty × Price for all orders. The full bill before anything is taken off.
- Discount = money taken off the bill.
- Returns = money given back for items sent back.
- Delivery = delivery fees customers paid.
- Net H1 / Net H2 = Net Sales in the first half (April–September) and the second half (October–March).
In the Power BI file, the same numbers come from an Orders table (one row = one order) linked to a Companies table. You will use it in the practice tasks below.
| Company | Cur | Orders | Gross | Discount | Returns | Delivery | Net H1 | Net H2 |
|---|---|---|---|---|---|---|---|---|
| Chai Point Cafes Food & beverage · Pune | ₹ | 6 | ₹14,775 | ₹476 | ₹655 | ₹220 | ₹9,265 | ₹4,379 |
| Sahyadri Pharma Pharma · Nashik | ₹ | 6 | ₹43,980 | ₹3,908 | ₹2,170 | ₹140 | ₹18,421 | ₹19,481 |
| Thames Bikes Bicycle shop · London | £ | 6 | £13,305 | £508 | £1,990 | £16 | £6,749 | £4,058 |
| Brooklyn Bakes Bakery · New York | $ | 6 | $893 | $45 | $0 | $14 | $472 | $376 |
| Berlin SoftWorks Software · Berlin | € | 6 | €9,910 | €447 | €215 | €17 | €5,222 | €4,026 |
KPI dashboard for Chai Point Cafes
This is how the 5 KPIs look as cards on a report page. All numbers come from the first row of the table.
1 Total Revenue
Why
The owner first asks: how much money came in from selling? Total Revenue answers this in one number.
What
Total Revenue is all money that comes in from selling. Here it is Net Sales plus the delivery fees customers paid.
- Net Sales = sales money after discounts and returns (KPI 3)
- Delivery fee = extra money the customer paid for delivery
How (in Power BI)
- On the Home tab, click New measure.
- Type the name
Total Revenue =. - Use SUMX over the Orders table, so Power BI works one row at a time.
- On each row: Qty × Price, minus Discount, minus Returns, plus DeliveryFee.
- Press Enter. Put the measure on a Card visual.
Total Revenue = SUMX(Orders, Orders[Qty] * Orders[Price] - Orders[Discount] - Orders[Returns] + Orders[DeliveryFee])2 Gross Sales
Why
Gross Sales shows the size of demand: how much customers wanted to buy, before any cuts.
What
Gross Sales is the full bill: units sold × price of one unit, added up for all orders. Nothing is taken off yet.
- Qty = units sold in one order
- Price = price of one unit
How (in Power BI)
- On the Home tab, click New measure.
- Type
Gross Sales =. - Use SUMX over Orders, because the multiply must happen on each row.
- On each row multiply Qty by Price.
- Press Enter and show it on a Card.
Gross Sales = SUMX(Orders, Orders[Qty] * Orders[Price])3 Net Sales
Why
Discounts and returns eat into sales. Net Sales shows the sales money we really keep.
What
Net Sales = Gross Sales − Discounts − Returns.
- Discount = money taken off the bill
- Returns = money given back when a customer sends items back
How (in Power BI)
- On the Home tab, click New measure.
- Type
Net Sales =. - Use SUMX over Orders.
- On each row: Qty × Price, minus Discount, minus Returns.
- Press Enter. Compare it with Gross Sales on two cards.
Net Sales = SUMX(Orders, Orders[Qty] * Orders[Price] - Orders[Discount] - Orders[Returns])4 Average Order Value (AOV)
Why
If AOV goes up, each customer visit brings more money. Shops push combos and "add one more" offers to raise it.
What
AOV is the average money from one order. AOV = Net Sales ÷ number of orders.
- Number of orders = count of rows in the Orders table
- DIVIDE = a safe divide. It gives blank instead of an error when the bottom is 0
How (in Power BI)
- On the Home tab, click New measure.
- Type
AOV =. - Top part: Net Sales (the same SUMX as KPI 3).
- Bottom part: COUNTROWS of Orders.
- Put both inside DIVIDE. Press Enter.
AOV = DIVIDE(SUMX(Orders, Orders[Qty] * Orders[Price] - Orders[Discount] - Orders[Returns]), COUNTROWS(Orders))5 Sales Growth %
Why
Growth tells if the business is getting bigger or smaller. Investors and managers check it first.
What
Sales Growth % = (this period − last period) ÷ last period. Here we compare H2 with H1.
- H1 = April–September
- H2 = October–March
- CALCULATE = runs a measure with an extra filter, like Half = "H2"
How (in Power BI)
- On the Home tab, click New measure.
- Make Net Sales for H2 with CALCULATE and the filter Half = "H2".
- Make Net Sales for H1 the same way.
- Take H2 minus H1.
- DIVIDE that by H1. Format the measure as a percentage.
Sales Growth % =
VAR NowSales = CALCULATE([Net Sales], Orders[Half] = "H2")
VAR LastSales = CALCULATE([Net Sales], Orders[Half] = "H1")
RETURN DIVIDE(NowSales - LastSales, LastSales)Comparison card
| KPI | Takes off discounts? | Takes off returns? | Adds delivery fees? | Chai Point |
|---|---|---|---|---|
| Gross Sales | No | No | No | ₹14,775 |
| Net Sales | Yes | Yes | No | ₹13,644 |
| Total Revenue | Yes | Yes | Yes | ₹13,864 |
Interview cards
Gross Sales is the full bill. Net Sales is Gross Sales minus discounts and returns.
If there are no orders, / gives an error. DIVIDE gives blank, so the card stays clean.
No. Convert to one currency first with an exchange-rate table, or show each company separately.
It is a ratio of two totals. It must change with slicers, so it is a measure.
Revision card
- Gross Sales = Qty × Price, all orders
- Net Sales = Gross − Discount − Returns
- Total Revenue = Net Sales + other sales income (delivery fees)
- AOV = Net Sales ÷ Orders
- Sales Growth % = (Now − Last) ÷ Last
Quick quiz
1. A shop has Gross Sales ₹10,000, discount ₹1,000 and returns ₹500. What is Net Sales?
2. Net Sales is $2,000 from 40 orders. What is AOV?
3. Last half £400, this half £500. Sales Growth % is…
4. Which KPI does not take off returns?
5. Which DAX function is safe when the bottom number is 0?
Practice tasks
Loading DAX practice tasks...
- Gross Sales (Easy): Shown per Company. Measure: Gross Sales = Qty × Price, added up for all orders.
- Total Discount (Easy): Shown per Company. Measure: Total Discount = sum of the Discount column.
- Total Returns (Easy): Shown per Company. Measure: Total Returns = sum of the Returns column.
- Net Sales (Medium): Shown per Company. Measure: Net Sales = Qty × Price − Discount − Returns, for all orders.
- Total Revenue (Medium): Shown per Company. Measure: Total Revenue = Net Sales + DeliveryFee, for all orders.
- Order Count (Easy): Shown per Company. Measure: Orders = number of rows in Orders.
- Average Order Value (AOV) (Medium): Shown per Company. Measure: AOV = Net Sales ÷ number of orders.
- Unique Customers (Easy): Shown per Company. Measure: Customers = how many different CustomerID values.
- Sales per Customer (Medium): Shown per Company. Measure: Sales per Customer = Net Sales ÷ number of different customers.
- Return Rate % (Medium): Shown per Company. Measure: Return Rate % = Returns ÷ Gross Sales.
- Net Sales in H2 (Medium): Shown per Company. Measure: Net Sales H2 = Net Sales only for rows where Half is "H2".
- Sales Growth % (H2 vs H1) (Hard): Shown per Company. Measure: Sales Growth % = (Net Sales H2 − Net Sales H1) ÷ Net Sales H1.