Forum Discussion
Separate Measure into positive and negative values
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?
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].
6 Replies
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!
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.
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.
