Forum Discussion

philtab's avatar
philtab
Frequent Visitor
9 years ago
Solved

Sum negative values for groups

Hi all,

 

I have somehow a tricky problem.

I want to create a measure which sums the negative values per Company and Account:

 

CompanyAccountValue
AK10010
AK100-10
AK20020
BK10020
BK100-30
BK200-40
CK10010
CK20020
CK200-30

 

So for the table above I would like to get the result of -60:

 

> -10 for Company B and Account K100

> -40 for Company B and Account K200

> -10 for Company C and Account K200

 

I am only able to sum all negative values with the result of -110....

 

Any chance that I can group within the measure by Company and Account?

 

Any help would be much appreciated.

 

Many thanks

Philip

Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    philtab

     

     

    Hi, please try with this measure:

     

    SumNegatives =
SUMX (
    SUMMARIZE (
        Table1,
        Table1[Company],
        Table1[Account],
        "SumValues", CALCULATE ( SUM ( Table1[Value] ) )
    ),
    IF ( [SumValues] < 0, [SumValues], 0 )
)

    Regards

     

    Victor

    Lima - Peru

2 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    philtab

     

     

    Hi, please try with this measure:

     

    SumNegatives =
SUMX (
    SUMMARIZE (
        Table1,
        Table1[Company],
        Table1[Account],
        "SumValues", CALCULATE ( SUM ( Table1[Value] ) )
    ),
    IF ( [SumValues] < 0, [SumValues], 0 )
)

    Regards

     

    Victor

    Lima - Peru

    • philtab's avatar
      philtab
      Frequent Visitor
      9 years ago

      Victor,

      many thanks! You saved my day, thats it!

       

      Best

      Philip