topic DAX Function not summing in DAX Commands and Tips

DAX Function not summing

Anonymous — Mon, 11 Jul 2022 13:43:23 GMT

Good Afternoon,

I have tried to build a report which takes two tables from our system, apppends them on top of one another in order to create one table and then use this as my dataset.

Within this table, I have applied a number of filters as I only want to bring in certain data.

I have a DAX function which is calculating the number of order available in the current month. It looks like this:

Orders Available in Month DAX =

VAR ordersAvlbyCust = IF(SUMX('JDE Tables Appended', 'JDE Tables Appended'[Forward Orders]) > SUMX('JDE Tables Appended', 'JDE Tables Appended'[Orders Available in Month]), 0,

SUMX('JDE Tables Appended', 'JDE Tables Appended'[Orders Available in Month]) + SUMX('JDE Tables Appended', 'JDE Tables Appended'[Credit Notes]) - SUMX('JDE Tables Appended', 'JDE Tables Appended'[Forward Orders]))

VAR orderAvlTotal = CALCULATE(SUM('JDE Tables Appended'[Orders Available in Month]) + SUM('JDE Tables Appended'[Credit Notes]) - SUM('JDE Tables Appended'[Forward Orders]),

FILTER('JDE Tables Appended', 'JDE Tables Appended'[Orders Available in Month]), FILTER('JDE Tables Appended', 'JDE Tables Appended'[Credit Notes]), FILTER('JDE Tables Appended', 'JDE Tables Appended'[Forward Orders]))

RETURN

IF(

HASONEVALUE('JDE F0101'[Address Number]),

ordersAvlbyCust, orderAvlTotal) /100000

However, this does not show a total at the bottom of the table?

Can anyone help me get this total on?

Thanks,

Craig

Re: DAX Function not summing

tamerj1 — Mon, 11 Jul 2022 13:50:06 GMT

Hi Anonymous
Please try

Orders Available in Month DAX = SUMX ( VALUES ( 'JDE F0101'[Address Number] ), CALCULATE ( VAR ordersAvlbyCust = IF ( SUMX ( 'JDE Tables Appended', 'JDE Tables Appended'[Forward Orders] ) > SUMX ( 'JDE Tables Appended', 'JDE Tables Appended'[Orders Available in Month] ), 0, SUMX ( 'JDE Tables Appended', 'JDE Tables Appended'[Orders Available in Month] ) + SUMX ( 'JDE Tables Appended', 'JDE Tables Appended'[Credit Notes] ) - SUMX ( 'JDE Tables Appended', 'JDE Tables Appended'[Forward Orders] ) ) RETURN DIVIDE ( ordersAvlbyCust, 100000 ) ) )

Re: DAX Function not summing

Anonymous — Mon, 11 Jul 2022 13:54:17 GMT

Perfect - thank you so much!!

Re: DAX Function not summing

Anonymous — Mon, 11 Jul 2022 14:52:21 GMT

If I add a variance column into the model, I have the same issue??

Outlook vs Orders in Month = 'Outlook File'[Outlook in GBP] - [Orders Available in Month DAX]

This gives a total which is far greater than what it should be. What would you suggest? SUMX? SUMMARIZE?

Re: DAX Function not summing

tamerj1 — Mon, 11 Jul 2022 15:03:38 GMT

Anonymous
Please help me out with a screenshot so I can better understand

Re: DAX Function not summing

Anonymous — Tue, 12 Jul 2022 13:37:02 GMT

I have a new measure which references "Orders Available in Month DAX" which you have helped me with yesterday (thank you :-D) as per below:

Invoiced vs Orders in Month = ([Calculation of Sales in Month] - [Orders Available in Month DAX])

Calculaution of Sales in month is worked out as the following:

Calculation of Sales in Month = SUM('JDE Tables Appended'[Sales in Month])/100000

However, when I do one take the other, the total is incorrect. The row calculates correctly but the overall total doesn't sum correctly. Please help me 😄

The total currently works out at plus 906.50 (which is the same as the sales column). The total should be -1,758.

Re: DAX Function not summing

tamerj1 — Tue, 12 Jul 2022 14:09:57 GMT

Anonymous
Please try

Orders Available in Month DAX = SUMX ( SUMMARIZE ( 'JDE F0101', 'JDE F0101'[Address Number], 'JDE F0101'[Address Name] ), CALCULATE ( VAR ordersAvlbyCust = IF ( SUMX ( 'JDE Tables Appended', 'JDE Tables Appended'[Forward Orders] ) > SUMX ( 'JDE Tables Appended', 'JDE Tables Appended'[Orders Available in Month] ), 0, SUMX ( 'JDE Tables Appended', 'JDE Tables Appended'[Orders Available in Month] ) + SUMX ( 'JDE Tables Appended', 'JDE Tables Appended'[Credit Notes] ) - SUMX ( 'JDE Tables Appended', 'JDE Tables Appended'[Forward Orders] ) ) RETURN DIVIDE ( ordersAvlbyCust, 100000 ) ) )