sum up negative values of a measure

[Anonymous]

Hi guys, hope you are doing well and are not tired to help a newbie with his problem.

A table includes the sales by cusotmers of Q1 2020 and Q1 2021. With a measure, I calculate the difference of the amount between both quarters:

DiffAmount = 
    CALCULATE(
        Sum('Sales'[Amount]), 'Sales'[Quarter] = "Q1 2021") - 
    CALCULATE(
        Sum('Sales'[Amount]), 'Sales'[Quarter] = "Q1 2020")

As you can surely imagine, some differences are negative. What I'am trying to figure out is, how can I create a measure which only sums up the negative differences in total of the first measure? I tried this measure, but SUM needs a column as expression:

TotalNegativeDiffAmout = 
    CALCULATE(
        SUM([DiffAmount], [DiffAmount]<0)

Thanks for your help,

MRT

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Here is the measure for difference.

Difference = SUM('Table'[Value2])-SUM('Table'[Value1])

You may create a measure like below.

Result1 = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Customer],
    "Diff",
    SUM('Table'[Value2])-SUM('Table'[Value1])
)
return 
SUMX(
    FILTER(
        tab,
        [Diff]<0
    ),
    [Diff]
)

Or

Result2 = 
SUMX(
    FILTER(
        'Table',
        [Difference]<0
    ),
    [Difference]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Allen,

thanks for your support. Both measures work fine. To explain solution 1 with my own words: With var tab the measure creates a table based on column [Customer] in tab 'Table', the measure then adds a new column "Diff" which returns the differences of Q1 20 and 21. With return only those values are considered as they are true to SUMX. And SUMX is used instead of SUM, because only SUMX can sum up the values of filtered values of column "Diff".

[vanessafvg]

not quite sure i understand you can you demonstrate what result you are looking for?

[Anonymous]

Hi vanessafvg,

for example with the first measure I know, that customer A has a difference of +10, B of -5 and C of -12. The second measure should sum the differences of B and C, so that the result is -17.