Forum Discussion

SAGUILAR's avatar
SAGUILAR
Frequent Visitor
2 years ago
Solved

Sum only positive

Hello,

 

I'm triying to show the total of the positive values only from a measure (Inv. Expected) that I created that substracts and sums several columns.

In this scenario, the total includes the -58.56. I don't want to consider that value, I need to show the result of the sum of all the positives.

Thanks!!!

 

Need Help
  • collinq's avatar
    collinq
    2 years ago

    Hey SAGUILAR ,

     

    I think that formula in Dax would be something like "CALCULATE(SUM('Table'[field],'Table'[field] >0)"

    Let us know if that works to do what you want.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Thank you collinq 

    Hi, SAGUILAR 

    Based on your description, I created a simple dataset:

    You mentioned that the column you are currently showing is a metric, so I simply created a measure as follows:

    In order to sum only the positive values in total, I created a measure using the following DAX expression while keeping the original measure:

    MEASURE =
IF (
    ISINSCOPE ( 'Table'[Id] ),
    'Table'[Amounts],
    SUMX ( FILTER ( 'Table', 'Table'[Amounts] >= 0 ), 'Table'[Amounts] )
)

    Here are the results:

    I've uploaded the PBIX file I used this time below.

     

     

    Best Regards

    Jianpeng Li

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

     

     

2 Replies

  • collinq's avatar
    collinq
    Super User
    2 years ago

    Hey SAGUILAR ,

     

    I think that formula in Dax would be something like "CALCULATE(SUM('Table'[field],'Table'[field] >0)"

    Let us know if that works to do what you want.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you collinq 

    Hi, SAGUILAR 

    Based on your description, I created a simple dataset:

    You mentioned that the column you are currently showing is a metric, so I simply created a measure as follows:

    In order to sum only the positive values in total, I created a measure using the following DAX expression while keeping the original measure:

    MEASURE =
IF (
    ISINSCOPE ( 'Table'[Id] ),
    'Table'[Amounts],
    SUMX ( FILTER ( 'Table', 'Table'[Amounts] >= 0 ), 'Table'[Amounts] )
)

    Here are the results:

    I've uploaded the PBIX file I used this time below.

     

     

    Best Regards

    Jianpeng Li

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