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

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