Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate total ignoring negative

Hi everyone,

I'm trying to calculate total amount ignoring -ve values in the sum but displaying them on the table. For example I want to calculate total for Apples, Oranges and Banana's whereby the total is 3,000 but not 2,500 but the 500 should be shown on the table. When i filter the sum for values > 0 it shows 0 on Banana's and I'd like it to show the actual amount

Apples 1,000
Oranges2,000
Banana's -500
Total Amount 3000


Thanks

  • johnt75's avatar
    johnt75
    4 years ago

    You could return different values based on whether an individual item was in scope or not, e.g.

    Amount = IF( ISINSCOPE( 'Table'[Fruit] ), SUM('Table'[Value]), 
CALCULATE( SUM('Table'[Value]), 'Table'[Value] > 0 )
)

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    You could return different values based on whether an individual item was in scope or not, e.g.

    Amount = IF( ISINSCOPE( 'Table'[Fruit] ), SUM('Table'[Value]), 
CALCULATE( SUM('Table'[Value]), 'Table'[Value] > 0 )
)