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.
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.
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.
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.
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.
IF statement to evaluate if the current row is a single value or the total row.SUMX function to iterate through the virtual table and sum the adjusted project revenues.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.
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.