Forum Discussion
Separate Measure into positive and negative values
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].
Power ParticipantWithout 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.
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
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].