How to Solve the Total Row with DAX in Power BI Desktop

   In this tutorial, Mitchell Pearson from Pragmatic Works explores the challenges with total rows in Power BI Desktop and provides a solution for handling them with DAX. Power BI’s total rows are not as straightforward as they might seem, especially when you want them to behave as expected. Here’s a breakdown of how to solve this common problem.

Understanding the Issue

Mitchell begins by explaining that total rows in Power BI do not behave the same way as Excel. While Excel will simply sum the values in the column, Power BI considers the context and may return unexpected results. In this case, Mitchell uses a simple dataset with project revenue and costs, demonstrating how these totals work under filter contexts.

Scenario: Conditional Logic for Revenue

The issue arises when you apply conditional logic to your DAX measures. For example, you might not want to count revenue for a project that has no associated costs. Mitchell creates a new DAX measure with an IF condition to set the project revenue to zero when there are no costs. While this works as expected on a row level, it doesn’t give the desired outcome for the total row, which still shows an incorrect total of $1,100 instead of the expected $900.

Why the Total Row Isn’t What You Expect

At the total row, Power BI does not just sum the values in the column. Instead, it calculates the measure based on the entire filter context, which includes all projects, not just the ones with valid costs. As a result, the total row ends up showing the sum of all the project revenues, including those with zero costs. This behavior can be confusing for users unfamiliar with Power BI’s filter context.

Solution: Using Virtual Tables and DAX Functions

Mitchell then demonstrates how to adjust the DAX calculation to handle the total row correctly. He proposes using a virtual table to summarize the data and perform the calculation for each row in the table separately. This allows for the expected total to be calculated by summing the adjusted project revenue values, not the raw ones.

Steps to Solve the Total Row Issue

1. Start by building a new DAX measure that checks if the row is a total row.
2. Use the IF statement to evaluate if the current row is a single value or the total row.
3. If it’s not the total row, apply the original conditional logic to calculate the project revenue.
4. If it’s the total row, create a virtual table with all the relevant projects in the current filter context.
5. Use the SUMX function to iterate through the virtual table and sum the adjusted project revenues.

Testing and Verifying the Results

After implementing these changes, Mitchell verifies that the calculation now correctly returns the expected result of $900 for the total row. He emphasizes the importance of using virtual tables and DAX functions such as SUMX to manage complex filter contexts in Power BI.

Final Thoughts

This issue with total rows is a common challenge faced by many Power BI users, especially when dealing with complex models. By understanding the underlying mechanics of Power BI’s filter context and using DAX effectively, you can ensure that your total rows behave as expected.

If you're facing similar challenges or want to dive deeper into Power BI and DAX, Pragmatic Works offers a virtual mentoring program where experts like Mitchell Pearson provide one-on-one support. For more tips and techniques on Power BI, visit

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.