I have used a small sample and I get the correct totals for the “Number of Originations” column. However using the same formula, I cannot get it to add up the correct total in the output. Please review my formula and also look at the model screen shots
[Fleet St Lifetime Interest.pbix|attachment](upFLEET ST LIFETIME INT.xlsx (594.2 KB) load://rSwANkaUWGQ2CttFkMCSEsJMI8i.pbix) (102.1 KB)
Your PBIX file didn’t upload, so I can’t say for 100% certain but I’m pretty sure that applying the approach in the thread below will solve your problem:
If it doesn’t, please repost your PBIX so that we can provide a more specific solution.
Thanks.
Well, this does not works since the sets of data are different. I will have to make a mini data file then. Essentially, given my formula, any “New origination” amounts when categorized by Tier or by period (see above) come out correctly. Given my count formula, I am able to see the correct counts for each row. My question is why does the total end up being 2? I don’t understand where this is coming from given that 1+1+1 = 3 and 1+2 = 3. This is what I am attempting to achieve.
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
@BrianJ,
Here are the results from my dummy data. As you can see, my totals randomly get me correct and incorrect answers. I am very interested in solving this using variables (VAR) formulas. However using HASONEVALUE to change from row context to context is also going to be useful for my future development. I appreciate your help.
New Originations.xlsx (2.3 MB)
New Originations.pbix (220.3 KB)
Ben
Please see the pbx and data that I have uploaded today…
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:
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.
I hope this is helpful. Full solution file posted below.
Hi Brian,
Thanks for your insights. Using this information, I changed a small part of the DAX formula and it gave me the correct results. I am uploading the pbx file in case you want to see what I have changed. Thanks for the successful collaboration.
