Incorrect column totals in final results

DAX DAX Calculations
7

@BenBen2,

Two solutions for the price of one today…

Solution 1
This is the way I’ve always done it in the past. Basic construct is to:

  1. Within a variable, use virtual table functions like SUMMARIZE or SELECTCOLUMNS to create the necessary evaluation contexts for your calculation
  2. Wrap that table in ADDCOLUMNS function to add a column for the measure you want to sum correctly
  3. In the RETURN statement of the measure, test whether the current row is a a total row or not (typically using HASONEVALUE, and in rarer cases ISINSCOPE). If it isn’t, just apply the original measure, otherwise total the relevant column from the virtual table.

More simply put, calculate the measure row by row, until it gets to the total line, then force it to total the column. Here’s what the measure built on this logic looks like:

Num Chgd Off Solut1 = 

VAR vTable =
ADDCOLUMNS(
    SUMMARIZE(
        'New Originations',
        'New Originations'[ ACCOUNT #],
        'Loan Types'[LOAN CATEGORY]
    ),
    "NumChgOff", [Number Charged-Off]
)

RETURN
IF( 
    HASONEVALUE( 'New Originations'[ ACCOUNT #] ),
    [Number Charged-Off],
    SUMX(
        vTable,
        [NumChgOff]
    )
)

Solution 2
The construct above has always worked well for me, but then I saw the video below from @sam.mckay.

https://forum.enterprisedna.co/t/fixing-complex-total-errors-dax-formula-concepts/598

His solution is much slicker and simpler – taking advantage of the fact that iterating functions like SUMX calculate a column total on a row by row basis. This measure accomplishes the same thing as the one above with much less code:

Num Chgd Off Solut2 = 

SUMX(
    SUMMARIZE(
        'New Originations',
        'New Originations'[ ACCOUNT #],
        'Loan Types'[LOAN CATEGORY]
    ),
    [Number Charged-Off]
)

As you can see from the screenshot below, both measures produce the correct total of 18.

image
image1305×608 43.6 KB

I hope this is helpful. Full solution file posted below.