Sum up all positive and all negative

[ThomasSan]

Hi everyone,

I am attempting to create a sum of all positive and all negative changes of sales values. My base table looks as follows:

Account	Year	Sales
A	1	10
A	2	15
B	1	10
B	2	6
C	1	10
C	2	10
D	1	10
D	2	11
E	1	10
E	2	9

As you can see, sales of accounts A, B, C, D and E between year 1 and 2 have changed as follows:

	Difference
A	5
B	-4
C	0
D	1
E	-1

Which means that in total, we have a positvie sales difference between year 1 and 2 of 6 (=5+1) and a total negative sales difference of -5 (=(-4)-(-1)).

I have created the first difference by creating a measure that looks as follows:

var salesPY=
CALCULATE(
    sum('Salestable'[Sales]),
        FILTER(
        'Salestable',
        'Salestable'[Year] = 1
    )
)

var salesCY=
CALCULATE(
    sum('Salestable'[Sales]),
        FILTER(
        'Salestable',
        'Salestable'[Year] = 2
    )
)

return diff=
salesCY - salesPY

Due to the presence of slicers and more details in the original table, I am not able to sensibley use a calculated column here. 

Can anyone tell me, how to aggregate all positive/negative sales differences together, so I get 6 and -5, respectively?

Thank you for your help in advance!

[Greg_Deckler · Accepted solution]

@ThomasSan Assuming your measure is called "Diff":

Measure Pos =
  VAR __Table = 
    ADDCOLUMNS(
      SUMMARIZE('Salestable',[Account]),
      "__Diff",[Diff]
    )
RETURN
  SUMX(FILTER(__Table, [__Diff] > 0),[__Diff])

Measure Neg =
  VAR __Table = 
    ADDCOLUMNS(
      SUMMARIZE('Salestable',[Account]),
      "__Diff",[Diff]
    )
RETURN
  SUMX(FILTER(__Table, [__Diff] < 0),[__Diff])

[ThomasSan]

Hi @Greg_Deckler ,

Your solution works perfectly and I got to learn something new! Thanks a lot!