Need positive values from another column with aggregated/sliced values

[deepanko]

Hi PowerBI commnity,

I am new to PowerBI and I could really appreiate your help here. So, I have a table and many slicers. Below, I have shared a snippet of my table and highlighted where I am having trouble. 

In the second column, I have summed the values in my PowerBI table from my excel column. In excel, this column had multiple poitive and negative value. 

Next I created a measure for the third column 

Measure = CALCULATE(sumx(sheet1,if(Sheet1["values"]>0,Sheet1["values"],0)))

So, I just want the positive values from the 2nd column to my 3rd column. The highighted pink values are not matching as there were a few negative values which according to my Measure logic did not get picked. But the green highlighted value is corect as there were no negative values.

Looking forward to your help.

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=if([measure]>0,[measure],0)

Measure is the one which is returning positive and negative values.

Hope this helps.

[deepanko]

Thank you so much @Ashish_Mathur 

So, using a dummy variable and the measure like you suggested I was able to get the positive values from the previous columns.

This is what i did:

Dummy = sum(Sheet1[I just want positive values from the previous column]) idea I got from (@karimkz - https://community.powerbi.com/t5/Desktop/IF-statement-in-Measure-need-help/m-p/135388)

Measure = IF([Dummy]>0,[Dummy],0) 


And now I have another issue. I am not getting the correct totals for the Measure column.

 I have attached the screenshot below. So, I came aross another one of your answer @Ashish_Mathur  where i tried to create 

Measure 2 = SUMX(VALUES(Sheet1[I just want positive values from the previous column]),[Measure])
However, due to my inexperience, I am not able to get this logic. And the highlighted values in pink are wrong. I just want the sum of the green values.

Really appreciate your help and thank you in advance.

[Ashish_Mathur]

Hi,

Try this

=SUMX(VALUES(DATA[Grouped By]),[Measure])

Hope this helps.