Totals not summing using advanced Dax example from video

DAX DAX Calculations
1

Hi,

I’m trying to use the method in the totals with advanced DAX logic, but it doesn’t seem to be working right.

Basically I just want a sum of the “mySum” variable.

Thanks,

RP UnitCost =
VAR mySum = IF(MIN(‘Bom Exploded’[Item Type]) = “R”,DIVIDE([Total Cost RP] , [Total RP Quantity] , 0) * SUM(‘Bom Exploded’[Quantity]) * ‘Item Value RP’[RP-W Item],DIVIDE([Total Cost RP] , [Total RP Quantity] , 0) )
VAR sumTotal = SUMMARIZE(‘Bom Exploded’, ‘Bom Exploded’[RP Item], “newSum”, mySum)

return IF(HASONEVALUE(‘Bom Exploded’[RP Item]), SUMX(sumTotal,[newSum]) , mySum )

2

Hi @MathewA, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

Here are some videos that may help you masking sensitive data and create datasets and data models representative of your problem:

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

3

Hello @MathewA, a gentle reminder on your PBIX File. This will help other users to find a solution to your inquiry.

In case we won’t see any activity from you in the coming days, we’ll be marking this post as solved.

4

Hi,

Have you tried your summarize in table view to look what exactly it doing .
if you provide your working pbix it will help us to find a solution for you.

Thanks,
Anurag

5

Hi @MathewA,

Regarding your RP UnitCost, I have several remarks.

First create your mySum variable in a measure.

Then, change your RP UnitCost with this code.

RP UnitCost =

VAR _vTable =
ADDCOLUMNS(
SUMMARIZE( ‘Bom Exploded’, ‘Bom Exploded’[RP Item] ),
“@newSum” , [mySum] )

RETURN
IF( HASONEVALUE( ‘Bom Exploded’[RP Item] ), [mySum], SUMX( _vTable, [@newSum] ) )

This measure should work if there is in your result table only the column “RP Item”.

Hope it will help.

Best regards,
JBocher

2 Likes
6

That did it, thanks so much.

Out of curiosity why did the addition of the addColumns make it work?

7

Hi @MathewA,

Great news :slight_smile:
In my solution, I’ve changed the measure with ADDCOLUMN, but I’ve also changed the IF statement after RETURN.

Honestly I don’t know… I always mix ADDCOLUMNS and SUMMARIZE when I want to create new columns.
Try to remove the ADDCOLUMN and put the new column in the SUMMARIZE to see if it’s still working :wink:

Best regards,
JBocher