Community › Power BI & SQL
Why Does Your Power BI Matrix Total Not Match the Sum of Individual Rows?
You have created a Power BI dashboard, added a Matrix visual, and everything looks correct. Each category shows the expected number, but when you reach the Grand Total row, something unexpected happens. The total does not match the sum of the individual rows. You check your source data, refresh the report, and even rewrite your DAX measure. But the total still looks different. Before assuming your calculation is wrong, understand one important concept in Power BI: Filter Context. QUICK TESTS TO FIND THE PROBLEM Before changing your DAX formula, perform these checks. 1. Check whether your measure uses DISTINCTCOUNT, AVERAGE, DIVIDE, or percentage calculations. 2. Compare the Grand Total with the sum of the visible row values. 3. Check whether the same customer, product, or transaction appears under multiple categories. 4. Check whether slicers and report filters are affecting the result. 5. Test the same measure in a simple Table visual to understand how it behaves. If your measure uses DISTINCTCOUNT or AVERAGE, the Grand Total may be correct even when it does not equal the sum of the individual rows. Let's understand why. WHAT IS ACTUALLY HAPPENING? Power BI does not always calculate the Grand Total by adding all the numbers displayed above it. Instead, it evaluates the same DAX measure again using the filter context of the total. Filter Context means the conditions under which Power BI evaluates a calculation. For example, when you display sales by region, Power BI calculates sales separately for each region. When it calculates the Grand Total, it evaluates the measure across all regions included in the current report filters. This is why some totals are different from the sum of individual rows. REAL-WORLD EXAMPLE: DISTINCT CUSTOMER COUNT Imagine you are working as a Business Analyst for an online shopping business. Your manager asks you to prepare a dashboard showing how many unique customers purchased products from each category. Your sales data looks like this: Order ID | Category | Customer ID 101 | Electronics | C001 102 | Electronics | C002 103 | Electronics | C003 104 | Furniture | C002 105 | Furniture | C004 Your task is to calculate unique customers for each category. STEP 1: CREATE A DAX MEASURE Open Power BI Desktop. Go to Modeling, select New Measure, and enter: Unique Customers = DISTINCTCOUNT(Sales[Customer ID]) STEP 2: ADD A MATRIX VISUAL Add Category to Rows. Add Unique Customers to Values. Your Matrix will display: Category | Unique Customers Electronics | 3 Furniture | 2 Grand Total | 4 Now you might think something is wrong. Electronics has 3 customers and Furniture has 2 customers. So why is the Grand Total 4 instead of 5? STEP 3: UNDERSTAND THE REASON Look carefully at the source data. Customer C002 purchased products from both Electronics and Furniture. Therefore, this customer appears in both category calculations. For Electronics: C001, C002, C003 = 3 unique customers. For Furniture: C002, C004 = 2 unique customers. But across the entire business: C001, C002, C003, C004 = 4 unique customers. The Grand Total is correct because DISTINCTCOUNT counts C002 only once across the entire dataset. STEP 4: SHOULD YOU FORCE THE TOTAL TO SHOW 5? That depends on your business requirement. If your manager wants the number of unique customers across the entire business, 4 is correct. If your manager wants the sum of category-level unique customer counts, the expected answer is 5. You can create another measure: Sum of Category Customers = SUMX( VALUES(Sales[Category]), [Unique Customers] ) This measure evaluates the unique customer count for each category and then adds those results. The output becomes: Category | Customer Count Electronics | 3 Furniture | 2 Grand Total | 5 Notice that both totals can be correct. They simply answer different business questions. This is an important lesson for anyone working with business reports: never change a DAX formula just to make the total look right. First understand what the number represents. OTHER CALCULATIONS WHERE TOTALS CAN BE DIFFERENT 1. AVERAGE Suppose two departments have average salaries of 30,000 and 50,000. Their overall average is not necessarily 40,000. If the departments have different numbers of employees, Power BI calculates the average using all employees rather than simply averaging the two displayed averages. 2. PERCENTAGE Suppose one region contributes 20% of sales and another contributes 30%. You cannot automatically add percentages from different calculations and expect the result to represent the overall business percentage. The total must be calculated using the correct overall numerator and denominator. 3. DISTINCTCOUNT A customer can purchase products from multiple categories. Adding category-level unique customers can count the same customer multiple times. HOW TO HANDLE TOTALS LIKE A BUSINESS ANALYST Whenever you build a Power BI dashboard, ask yourself three questions: 1. Does the business need the sum of individual rows or a recalculated overall result? 2. Is the measure additive or non-additive? 3. Does the Grand Total represent the actual business requirement? For additive measures such as total sales, the Grand Total generally equals the sum of the individual groups when the groups are mutually exclusive. For non-additive measures such as averages, percentages, and distinct counts, the Grand Total may need to be recalculated. YOUR POWER BI CHECKLIST 1. Understand what your DAX measure calculates. 2. Check whether duplicate entities exist across categories. 3. Understand Filter Context. 4. Validate the Grand Total against the original business requirement. 5. Use SUMX only when you specifically need to add evaluated row-level results. 6. Never assume a different Grand Total automatically means your DAX formula is incorrect. If you want to improve your Power BI and DAX skills, you can practice practical data problems for free on Cellgrids. The biggest difference between someone who knows Power BI and someone who understands data analysis is the ability to explain why a number appears in a report, not just how to calculate it. Have you ever spent time fixing a Power BI Grand Total only to discover that the original calculation was already correct? Share your experience.
0 answers
No answers yet. Log in to be the first to answer.