How to Display the Correct Total in Every Row Using DAX Measures in Excel
Question details
The user needs to create a DAX measure that correctly calculates and displays a total for each selected point ID row while maintaining accurate overall grand totals.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Writing complex DAX formulas in a Data Model where the default filter context causes totals to display incorrectly on individual detail rows or the grand total row.
- Observed behavior
- To accurately present the calculated total in every row, the measure must separate the evaluation logic for detail rows from the total rows using advanced conditional DAX logic.
Ensure your dataset is added to the Excel Data Model (Power Pivot) and that you have basic familiarity with writing and applying DAX measures.
Use Conditional Logic to Handle Detail Rows and Totals Separately
This is the most robust method for displaying accurate totals, as it applies different calculation contexts depending on whether a single item or the grand total is being evaluated.
When using DAX, the grand total is not automatically a sum of the rows above it; it is evaluated in its own filter context. By combining HASONEVALUE, CALCULATE, and AVERAGEX within variables, you can force the Data Model to average the measure across the selected items properly.
Start your measure by defining a variable using the `HASONEVALUE` function (e.g., `VAR IsOneUtility = HASONEVALUE('Points'[Utility])`). This checks if the current row context is evaluating a single utility.
Create a second variable for the individual row calculation (e.g., `UtilityAverage`). Use the `CALCULATE` function combined with `ALL` or `ALLSELECTED` to remove the specific point filter so the rate calculates correctly for that item.
Create a third variable (e.g., `TotalAverage`) that uses `AVERAGEX` wrapped in `CALCULATE`. This iterates over the selected utilities and applies your base measure to find the correct total average.
End the measure with an `IF` statement: `RETURN IF(IsOneUtility, UtilityAverage, TotalAverage)`. This outputs the individual rate on data rows and the aggregated average on the grand total row.
Use ALLSELECTED for a Simplified Total Across Every Row
If your goal is simply to repeat the currently selected grand total value on every single detail row without complex averaging, a simplified CALCULATE function is highly effective.
Looking for a Lightweight, Reliable Office Suite?
While advanced DAX data modeling (Power Pivot) is a feature exclusive to Microsoft Excel, WPS Office Spreadsheet provides a powerful, free alternative for everyday data analysis. With robust standard PivotTables, advanced formulas, and seamless format compatibility, WPS handles your daily reporting needs effortlessly without expensive subscriptions.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your PC or Mac.
- 2. Open Your Excel File: Launch WPS Spreadsheet and seamlessly open your existing .xlsx workbooks.
- 3. Analyze with PivotTables: Select your data range, go to the Insert tab, and create a PivotTable to start analyzing your data immediately.
Frequently Asked Questions
What is the difference between ALL and ALLSELECTED in a DAX measure?
The ALL function completely removes any filters applied to the specified table or column, returning every possible row regardless of user selections. ALLSELECTED, however, removes the filters created by the current row or column context inside the visual (like a PivotTable row) but preserves explicit filters applied by the user, such as slicers or report-level filters.
Why does my DAX measure show the wrong grand total in a PivotTable?
In DAX, grand totals do not automatically add up the values of the rows displayed above them. Instead, the grand total evaluates the measure using the total filter context (where no individual row filters apply). To fix this, you often need to use iterating functions like SUMX or conditional logic (like HASONEVALUE) to force the total to aggregate correctly.
Can I use HASONEVALUE to check multiple columns at once?
No, HASONEVALUE evaluates only a single column and returns TRUE if exactly one distinct value is present in the current filter context. If you need to check multiple columns simultaneously, you must write multiple HASONEVALUE functions and combine them using logical operators like AND (&&).
