Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Total always 0

Hi all

I am not super expert in this kind of stuff so I need your help.

I have a sales amount per sales document in different days. My goal is to make the sum of the canceled order (Canc OR 5). When can the order be considered canceled? When  the sum of the amount of that order is zero and I have negatives: those negatives have to be added up.

As you can see in the screenshot the order ending with 478 has Canc OR = 0, same the 350 (for both the total sum is not zero); the total amount for order 344 is zero and it has 2 negatives (-388 and -6330).

Till now all is correct.

The problem is on total: I would like to get -6718 (the sum of -388 and -6330); in general I want to have the sum of column OR Canc. How could I do?

 

Here the code:

 

Canc OR 5 =
 
var checkval=calculate(
[Orders Received Sales Amount],
 
REMOVEFILTERS ( 'Sales' ),
VALUES ( 'Sales'[Sales Document] )
)

var neg_ord=calculate([Orders Received Sales Amount],
filter('Sales',[Orders Received Sales Amount]<0)
)

var canc_ord=

SUMX(
VALUES( 'Sales'[Sales Document]),
CALCULATE(
IF(
HASONEVALUE('Sales'[Sales Document]),
IF(
checkval=0,
        neg_ord,
        0
 
),
""
)
)
)

 

 

Thanks!

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    Please try

    Canc OR 5 =
SUMX (
    VALUES ( 'Sales'[Sales Document] ),
    CALCULATE (
        VAR checkval =
            CALCULATE (
                [Orders Received Sales Amount],
                REMOVEFILTERS ( 'Sales' ),
                VALUES ( 'Sales'[Sales Document] )
            )
        VAR neg_ord =
            CALCULATE (
                [Orders Received Sales Amount],
                FILTER ( 'Sales', [Orders Received Sales Amount] < 0 )
            )
        VAR canc_ord =
            IF ( checkval = 0, neg_ord )
        RETURN
            canc_ord
    )
)

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 
    Please try

    Canc OR 5 =
SUMX (
    VALUES ( 'Sales'[Sales Document] ),
    CALCULATE (
        VAR checkval =
            CALCULATE (
                [Orders Received Sales Amount],
                REMOVEFILTERS ( 'Sales' ),
                VALUES ( 'Sales'[Sales Document] )
            )
        VAR neg_ord =
            CALCULATE (
                [Orders Received Sales Amount],
                FILTER ( 'Sales', [Orders Received Sales Amount] < 0 )
            )
        VAR canc_ord =
            IF ( checkval = 0, neg_ord )
        RETURN
            canc_ord
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Great, tamerj1, it works!! Thanks