How to sum a measure when this measure greater than 0

[wangjuan303]

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

[danextian]

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

Alternative formula

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

[wangjuan303]

@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

[danextian]

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

[wangjuan303]

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, 

[danextian · 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
        )
    )
)

[wangjuan303]

Thank you for your help