When building reports in Power BI, two of the most common questions are: “How do I show a running total over time?” and “How do I identify my top customers by revenue?” Both answers lie in DAX, the formula language that powers calculations in Power BI.
In this article, we’ll walk through step-by-step examples of creating a running total measure and a Top N Customers measure. These are simple but powerful building blocks for financial reporting, customer dashboards, and sales analysis. Whether you’re connecting data from Xero, HubSpot, or Microsoft Dynamics, these DAX patterns will help you turn raw numbers into actionable insights.
What is a running total in Power BI and why does it matter?
A running total shows the cumulative value of a measure across time. For example, if you’re tracking monthly sales, a running total line shows how those sales add up month after month. This is especially helpful for finance and operations teams who need to see progress against annual revenue targets or budget forecasts.
Running totals also highlight seasonality, sales momentum, or periods where performance slows. Rather than seeing isolated monthly values, you can spot the shape of growth.
How to build a running total measure in Power BI
Let’s say you have a fact table called Sales with a column Sales[Amount] and a date field Sales[Date]. A basic measure for total sales looks like this:
Total Sales = SUM(Sales[Amount])To create a running total, we’ll wrap that measure inside a CALCULATE and use FILTER to ensure all dates up to the current one are included:
Running Total =
CALCULATE(
[Total Sales],
FILTER(
ALL(Sales[Date]),
Sales[Date] <= MAX(Sales[Date])
)
)
This measure tells Power BI: “Take the Total Sales and add it up for all dates up to the maximum date in the current context.”
Once created, drop the Running Total measure into a line chart with Sales[Date] on the X-axis. You’ll see a cumulative line that climbs steadily as time moves forward.
Alternatively, if your model has a proper Date table marked as a Date Table in Power BI, you can use the built-in function TOTALYTD for year-to-date values:
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])This is cleaner but slightly less flexible than the CALCULATE + FILTER pattern, which works for running totals across any time period.
For more tips on cleaning and preparing your Power BI data model before you start writing DAX, see our guide on Power BI Desktop Tips for Beginners.
What is a Top N Customers calculation and why is it useful?
Business leaders often want to know: “Who are our top five customers?” or “Which accounts generate the bulk of our revenue?” The Top N pattern in DAX lets you filter and rank customers based on a measure, such as sales amount.
This is especially useful when working with Xero or HubSpot data in Power BI. Instead of scrolling through thousands of customers, you can quickly highlight the most valuable accounts.
How to build a Top 5 Customers measure in Power BI
Start with the same Total Sales measure we used earlier. Next, use the TOPN function inside a CALCULATE to limit the result to the top five customers by revenue:
Top 5 Customers =
CALCULATE(
[Total Sales],
TOPN(
5,
VALUES(Customer[CustomerName]),
[Total Sales],
DESC
)
)
Here’s what’s happening:
- VALUES(Customer[CustomerName]) creates a list of all customers.
- TOPN(5, …, [Total Sales], DESC) ranks them by sales in descending order and returns only the top five.
- CALCULATE then evaluates Total Sales in the context of that reduced table.
Add this measure to a bar chart with Customer[CustomerName] on the Y-axis. You’ll see the five highest-revenue customers clearly listed.
If you’d like flexibility, you can even replace the number 5 with a parameter, so report viewers can choose a Top 3, Top 10, or Top 20 list dynamically.
Real-world use case: Finance and sales reporting
In practice, running totals and Top N measures are often used together. For example, a Power BI dashboard connected to Xero might show:
- A running total of invoices issued this financial year.
- A bar chart of the top five customers driving revenue.
This combination gives both a big-picture view of progress against annual goals and a detailed look at customer concentration. When paired with data from HubSpot or Dynamics 365, you can also analyse how customer pipeline activity translates into revenue growth.
For a walkthrough on connecting your Xero data into Power BI in just a few clicks, check out our guide on Connecting Xero to Power BI. If you’re more focused on marketing and sales, you might prefer our guide on Connecting HubSpot to Power BI.
Key takeaways
- Running totals show cumulative values across time, helping you track growth against targets.
- Top N Customers highlights your highest-value accounts, focusing attention where it matters.
- Both calculations rely on simple but powerful DAX patterns that any beginner can master.
With just a few lines of DAX, you can transform your Power BI reports from static tables into dynamic visuals that answer the most important business questions.
