DAX formula to sum amount with negative values

[juhoneyighot]

Hello!

I hope you could help me with this DAX formula that will be used.

I want to get to sum up all Name with negative amount in Amt Diff (Rev-Cost).

Do you know what DAX formula to use?

Thanks

[Poorva_Awale]

Hi All,

Even I am facing similar issue , my data contains both negative and positive values in column 'Revenue', however when i use the same column in my Table visual the negative values are neglected and the table shows only the positive values. I checked that this might be due to the summarization i am doing on the column to get the grand total. How do we handle this, do we have to do any kind of special formatting, etc? 

[Anonymous · Accepted solution]

Hi @juhoneyighot ,

I create a table as you mentioned.

Then I put it into the matrix visual.

Next I create a measure and get what you want.

Measure = 
VAR _RevCost =
    SUM ( 'Table'[Rev] ) - SUM ( 'Table'[Cost] )
RETURN
    IF ( _RevCost < 0, ( SELECTEDVALUE ( 'Table'[Name] ) ), _RevCost )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights]

@juhoneyighot,

Try this measure:

Sum of Negative Amount =
VAR vTable =
    ADDCOLUMNS ( VALUES ( 'Table'[Name] ), "@AmtDiff", [Amt Diff (Rev-Cost)] )
VAR vResult =
    SUMX ( FILTER ( vTable, [@AmtDiff] < 0 ), [@AmtDiff] )
RETURN
    vResult