cancel
Showing results for
Did you mean:  Helper II

## How to sum a measure when this measure greater than 0

Hi Team,

I am working n a report, need show "Diff" for two date,

in the "Diff" measure, some is greater than 0, it is the increase

some is less than 0, it is the decrease, I need to calculatethe total "Diff” where all Industries greater than 0,  (all the increased amount for Industry)

for example , I need get 128 for total increase. Here is related file link :

https://www.dropbox.com/s/h2yj6w6ylvjdtcd/Diff.pbix?dl=0

Thank you ahead for any idea for this measure

1 ACCEPTED SOLUTION  Super User

You can use and IF condition so it returns blank for those with negative [Diff]

``````Total Increase ALL =
IF (
[Diff] > 0,
CALCULATE (
[Diff],
FILTER (
ALL ( 'Fact Table'[Industry], 'Fact Table'[Scenario] ),
VAR _diff = [Diff] RETURN _diff > 0
)
)
)
``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
6 REPLIES 6  Super User

Hi @wangjuan303 ,

``````Total Increase =
SUMX (
SUMMARIZE (
'Fact Table',
'Fact Table'[Industry],
'Fact Table'[Scenario],
"Diff", [Diff]
),
"x", IF ( [Diff] > 0, [Diff] )
),
[x]
)`````` Alternative formula

``````Total Increase =
CALCULATE (
[Diff],
FILTER (
SUMMARIZE ( 'Fact Table', 'Fact Table'[Industry], 'Fact Table'[Scenario] ),
VAR _diff = [Diff] RETURN _diff > 0
)
)``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."  Helper II

@danextian  Thank you a lot for your suggestion, I test it, I need in "Total Increase" column always show total value "128" (total of all Industry where they are Diff >0) , then use this value to calculate for "Increase%", Do you have any idea, Thank you again   Super User

So what you want is to have 128 in all rows? Try this:

``````Total Increase ALL =
CALCULATE (
[Diff],
FILTER (
ALL ( 'Fact Table'[Industry], 'Fact Table'[Scenario] ),
VAR _diff = [Diff] RETURN _diff > 0
)
)`````` Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."  Helper II

Thank you so much, How can we just show Industry where Diff>0,  for example, in this report, "Total Increase ALL" should  like Total Increase show blank,   Super User

You can use and IF condition so it returns blank for those with negative [Diff]

``````Total Increase ALL =
IF (
[Diff] > 0,
CALCULATE (
[Diff],
FILTER (
ALL ( 'Fact Table'[Industry], 'Fact Table'[Scenario] ),
VAR _diff = [Diff] RETURN _diff > 0
)
)
)
``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."  Helper II

Thank you for your help Announcements #### The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic. #### Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate. #### What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world. Top Solution Authors
Top Kudoed Authors
Users online (3,153)