cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wangjuan303
Helper II
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, 

wangjuan303_0-1660521346731.png

 

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. 

wangjuan303_0-1660527858816.png

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

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."
www.linkedin.com/in/danebelarminocpa

View solution in original post

6 REPLIES 6
danextian
Super User
Super User

Hi @wangjuan303 ,

 

Please try this:

 

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

 

danextian_0-1660539846149.png

 

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."
www.linkedin.com/in/danebelarminocpa

@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

wangjuan303_0-1660565880967.png

 

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

danextian_0-1660566938705.png

 






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."
www.linkedin.com/in/danebelarminocpa

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, 

wangjuan303_0-1660573257552.png

 

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."
www.linkedin.com/in/danebelarminocpa

Thank you for your help

Helpful resources

Announcements
Power BI Show Episode 10 Recap

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

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.

Ignite 2022

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