Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ThomasSan
Helper IV
Helper IV

Sum up all positive and all negative

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:

AccountYearSales
A110
A215
B110
B26
C110
C210
D110
D211
E110
E29

 

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

 Difference
A5
B-4
C0
D1
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!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@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])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.