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. 

 

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

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    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
        Community 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

    Perfect - thank you so much!!

  • tamerj1's avatar
    tamerj1
    Community 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 )
    )
)