@BenBen2,

This all comes down to evaluation context and how Power BI evaluates measures and totals. In your second table for example, it steps through each GROUP item and calculates the number of originations - In-house Consumer, then Second Mortgage, and then Total. For the first two, it has data from your data model to correctly calculate the measure. Then it hits “Total” and doesn’t quite know what to do, since it is still in row-based evaluation mode but doesn’t have sufficient data to correctly calculate the total number of originations. I don’t know the exact algorithm it uses at this point, but it makes a best guess based on available information. Sometimes it gets it right (e.g., The Virtual Card Balance, New Originations, and Current Virtual Loan Balance columns) and sometimes gets it wrong (e.g., Number of Originations).

In situations where it gets the total wrong, you need to provide additional direction in the measure to give it awareness that when it hits the total row, it should switch approaches. The most common way to do this is through the HASONEVALUE() function. In your example, I would add a second variable called NumOriginAll, using REMOVEFILTERS() to remove the evaluation context filter on GROUP. Then the RETURN statement in your measure looks like this:

IF( HASONEVALUE( ‘Virtual Loan Types’[Group] ),

NumOrigin,

NumOriginAll

)

This basically says step through all the individual group items and apply the original measure, until you hit the total row (where Group has no defined value, thus HASONEVALUE (Group) = False) and then at that point apply the NumOriginAll measure.

On the face of it, the 1+1+1 = 2 and 1+2 = 2 results look crazy, until you realize that Power BI in this situation has no columnar “awareness” on its own (i.e., it is evaluating the total based on insufficient evaluation context information, not on the fact that the previous results were 1, 1, 1 or 1, 2).

Hope that’s helpful. If you still have problems with the specific measure revision, just post a PBIX file and I’m happy to work through the specific DAX code with you.

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