When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue.
When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX formula:
Minimum (Incorrect Total) =
MIN( [Total Sales], [Sales LY] )
The values for the detail rows would be correct but value for the total row would be incorrect.
In this example, the total row shows the minimum between the 2 total values rather than the total of the minimum column. To get the total of the minimum column, a virtual table can be constructed in the DAX measure
Minimum 1 (Correct Total) =
// DAX PATTERN NAME: Fix Incorrect Totals
// NOTES: Use ADDCOLUMNS to create a virtual table (consisting of the columns used in your table) inside an iterator
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
SUMX(
ADDCOLUMNS(
VALUES( Customers[Customer Name] ), -- column 1 of the table visual and virtual table
"Sales", [Total Sales], -- column 2 of the table visual and virtual table
"Sales LY", [Sales LY] -- column 3 of the table visual and virtual table
),
MIN( [Sales], [Sales LY] ) -- return the minimum of column 2 and column 3
)
NOTE: SUMMARIZE can also be used in place of ADDCOLUMNS, but be aware that when using calculated columns, it can lead to inconsistent results and performance issues.
Another method is to calculate separate values for the detail and total rows, and to use the HASONEVALUE() or ISINSCOPE() functions to test the context to see if one is on a detail row, e.g.:
Minimum 2 (Correct Total) =
// DAX PATTERN NAME: Fix Incorrect Totals
// NOTES: Use ADDCOLUMNS to create a virtual table (consisting of the columns used in your table) inside an iterator
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
IF(
HASONEVALUE( Customers[Customer Name] ),
MIN( [Total Sales], [Sales LY] ),
SUMX(
ADDCOLUMNS(
VALUES( Customers[Customer Name] ), -- column 1 of the table visual and virtual table
"Sales", [Total Sales], -- column 2 of the table visual and virtual table
"Sales LY", [Sales LY] -- column 3 of the table visual and virtual table
),
MIN( [Sales], [Sales LY] ) -- return the minimum of column 2 and column 3
)
)
Minimum 3 (Correct Total) =
// DAX PATTERN NAME: Fix Incorrect Totals
// NOTES: Use ADDCOLUMNS to create a virtual table (consisting of the columns used in your table) inside an iterator
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
IF(
ISINSCOPE( Customers[Customer Name] ),
MIN( [Total Sales], [Sales LY] ),
SUMX(
ADDCOLUMNS(
VALUES( Customers[Customer Name] ), -- column 1 of the table visual and virtual table
"Sales", [Total Sales], -- column 2 of the table visual and virtual table
"Sales LY", [Sales LY] -- column 3 of the table visual and virtual table
),
MIN( [Sales], [Sales LY] ) -- return the minimum of column 2 and column 3
)
)
This situation can also occur when performing a Pareto analysis, where once again the context must be taken into account. To get, for example, the total sales of the Top 20% of Customers in each state, one might try a single measure:
Sales of Top 20% (Incorrect Total) =
VAR CustomerTop20Percent = DISTINCTCOUNT( Sales[Customer ID] ) * 0.2
RETURN
CALCULATE( [Total Sales],
FILTER( VALUES( Sales[Customer ID] ),
RANKX( VALUES( Sales[Customer ID] ), [Total Sales], , DESC ) <= CustomerTop20Percent ) )
Again, the values for the detail rows would be correct but the grand total would be incorrect, as it ignores the state context.
To get the correct grant total, use the measure branching technique and create another measure that includes the state context.
Sales of Top 20% (Correct Total) =
// DAX PATTERN NAME: Fix Incorrect Totals
// NOTES: Use ADDCOLUMNS to create a virtual table (consisting of the columns used in your table ) inside an iterator
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
SUMX(
ADDCOLUMNS(
VALUES
( Locations[State Code] ), -- column 1 of the table visual and virtual table
"Total Sales", [Total Sales], -- column 2 of the table visual and virtual table
"Total Sales of Top 20% Customers", [Sales of Top 20% (Incorrect Total)] -- use the base measure as column 3 of the virtual table
),
[Total Sales of Top 20% Customers] -- return column 3
)
DAX Patterns - Fix Incorrect Totals.pbix (364.4 KB)
Reference:
HASONEVALUE
Returns TRUE when the context for columnName has been filtered down to one distinct value only.
ISINSCOPE
Returns TRUE when the specified column is the level in a hierarchy of levels.
