cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## sum up negative values of a measure

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)``````

MRT

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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
Not applicable

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".

Super User

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

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Anonymous
Not applicable

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors