DAX Function not summing

[Anonymous]

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

[tamerj1]

@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 )
    )
)

[Anonymous]

Perfect - thank you so much!!

[tamerj1 · Accepted solution]

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 )
    )
)

[Anonymous]

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?

[tamerj1]

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

[Anonymous]

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.