SUM Multiple Measures Based on Criteria

[marchn]

Hi

I am trying to write some DAX to add 5 different measures together. However, I only want to add the measures, where their result is positive i.e. >0. Below are the 5 measures:

Advocate Partner Count Change = [Advocate Partner Count Comparison]-[Advocate Partner Count]

Engaged Count Change = [Engaged Count Comparison]-[Engaged Count]

Interested Count Change = [Interested Count Comparison]-[Interested Count]

Neutral Count Change = [Neutral Count Comparison]-[Neutral Count]

No / Blank Count Change = [No / Blank Count Comparison]-[No / Blank Count]

These 5 measures are just calculating the difference between other measures and the results could be positive, negative or 0. 

e.g.

Advocate Partner Count Change = 1

Engaged Count Change = -1

Interested Count Change = 0

Neutral Count Change = 1

No / Blank Count Change = 1 

Therefore, I would want my measure to return the result 3, so it ignores the -1 and the zero. I'm finding it difficult because I can't seem to use SUM, as it's a measure and not a field in a table. 

For completness, I will also later want to sum all the negative values, but I'm hoping that will be straightforward if I'm able to resolve the above. 

[ChiragGarg2512 · Accepted solution]

The most basic way would be to create variables, where there value is defined by an if statement and return the sum.

Measure =
var _m1 = if([Advocate Partner Count Change] > 0, [Advocate Partner Count Change], 0)
var _m2 = if([Engaged Count Change] > 0, [Engaged Count Change], 0)
var _m3 = if([Interested Count Change] > 0, [Interested Count Change], 0)
var _m4 = if([Neutral Count Change] > 0, [Neutral Count Change], 0)
var _m5 = if([No / Blank Count Change]>0, [No / Blank Count Change], 0)

return _m1+_m2+_m3+_m4+_m5

[marchn]

That's amazing thank you! I was trying to use VAR, as I had a feeling that was the route I needed, but I'm not familiar enough with the syntax yet. I'm making the jump from having marginally above average Excel skills, to Power BI and it seems to me to be where Excel meets simple programming language. Really appreciate you taking the time to answer. thank you.