How to calculate a sum of certain values created in a measure

[ashear]

Hi all! I am new to power Bi and could really use some help!

I have created calculated measures in Power BI which sum the "LY units" and "TY Units" based on a date range from my raw sales table.

I then created another measure which calculated the difference in units from LY/TY.

Now what I am trying to do is to get to two separete sums:

One sum of all the total positive "gain" unit differences to get a "Total Gain"
and one sum of all the total negative "Drain" unit differences to get a "Total Drain".

I cant seem to find a way to SUMX a measure, or even how to SUMX a measure and filter it to <0 or >0.

When we get those totals we would like to be able to bounce the TY/LY unit diff number against the "Total Gain" and "Total Drain" to know what part numbers are contributing the most to the drain and/or gain.

Here is a screenshot of how I can get those calculation in Excel, just trying to figure out how to get them in Power BI via DAX now.

Highlighted in green is the formuals I am trying to figure out

[Ashish_Mathur]

Hi,

Does this measure work

Gains = SUMX(filter(VALUES(Data[Partno]),[Diff]>0),[Diff])

Losses = SUMX(filter(VALUES(Data[Partno]),[Diff]<0),[Diff])

Hope this helps.

[Irwan · Accepted solution]

hello @ashear 

here is the DAX for SUMX <0 and >0

- SUMX<0

Adding divide to get the result above

- SUMX>0

adding divide to get above result

Else, tweak a little bit if you need in measure form.

Hope this will help.

Thank you.

[ashear]

Thank you Irwan! That did the trick, I think where I was having trouble was I wasnt refrencing the table and the measure, just trying to do the measure, which ment certain data fields werent showing up!

[Irwan]

hello @ashear 

glad to be a help.

Thank you.