Separate Measure into positive and negative values

[donaldo]

Hi all,

I have a running history of projects with an attached value and a timestamp, like so:

ID	Value	Timestamp
1	10	03-02-2019
1	12	10-02-2019
1	15	17-02-2019

I have two Measures one with the Value the current week and one with the value the previous week. From those two I have made a new measure called [Savings Change] which is [This week] - [Previous week].

Now I have multiple projects / IDs in this table and would like to SUM all the positives and negatives by themselves, so I could show the sum of increases and decreases separately. 

I've tried 

Gains = CALCULATE([Savings Change];[Savings Change] > 0)

which does not work (A function 'CALCULATE' has been used in a True/False...).

I've tried a SUMX variant I couldn't get to work either.

How can I count the positive and negative values of a single measure ([Savings Change]) separately?

[gooranga1]

I have done this for bar charts so I can colour them appropriately

Something like

Positive = if([Savings Change]>0,[Savings Change],Blank())

Negative = if([Savings Change]<0,[Savings Change],Blank())

That would give you negative and positive measures, you would need to decide what to do with zeros!

[donaldo]

hi @gooranga1 

Thanks for the suggestion. The current net result of [Savings Change] is negative.

The Positive ends up being BLANK because it's overall negative, doesn't separate the positive values. IF[Savings Change] > 0 (this would already evaluate as false).

I'm guessing I need some sort of SUMX function to evaluate per row.

[gooranga1]

Without seeing your data I don't know how you are slicing it our organising it a matrix/graph which is the key in making it work. Below is an example of using to 2 measures using this technique Pos Trend and Neg Trend. I have done several others grouped not just on time like this example.

[donaldo]

My table is below.

I have a measure which is the sum of column 3 by weeks. One for the current [This Week] and one for [Previous Week]. Then I have a 3rd measure which is [Savings Change] that's just [This Week] - [Previous Week].

I want to see all positive values so the sum of all the rows where [Savings Change] > 0 and one sum of all rows where [Savings Change] < 0 .

[gooranga1]

Yes apologies when I do this kind of analysis I model the data a bit before so I have the actual previous weeks totals on the row via mdx rather than having to calculate it via a dax measure.

I am sure there is a way to to do it in DAX but it will be more complicated that just the simple case I have given.

I have had a look at a few possible ideas but none will work to add up the overall positive and negative. 

https://community.powerbi.com/t5/Desktop/Sum-of-previous-day-Sales-by-week/td-p/439363

https://community.powerbi.com/t5/Desktop/Slice-by-measure-result-positive-vs-negative/td-p/78153

[donaldo · Accepted solution]

Yeah, I managed to do it with some violence to 2 tables. So I have IDs and [Savings Change] for values, then I can via Visual Filter filter [Savings Change] > 0 and Hide the rest of the table to only show subtotal. But it's not really what I wanted, would have been preferable with a KPI card like the one above which is just [Savings Change].