Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Function not summing

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.    With...
  • tamerj1's avatar
    tamerj1
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.