Total of an IF statement

[michael_knight]

Hi,

I'm trying to use an IF statement within a Table visual and I'm having issues with the total column.

Margin Measure = CALCULATE(SUMX(FILTER(Sheet1, Sheet1[Agent] = "John"), Sheet1[Margin]), USERELATIONSHIP('Date'[Full Date], Sheet1[Date]))

Total = IF([Margin Measure] >=100, 1000, BLANK()) 

Those are the measure that I use in this table:

As you can see, the total isn't correct. It this case the total should be 2000

Does anyone know how to fix this?

https://www.dropbox.com/s/yl5ypb8tz8asim7/help%20-%20Copy.pbix?dl=0

Thanks

[Fowmy]

@michael_knight 

You can make your relationship active between Date and Sheet tables and modify the measure as below:

Total = 
SUMX(
    SUMMARIZE(Sheet1,Agents[Agent],'Date'[Month/Year]),
    IF([Margin Measure] >=100, 1000, BLANK()) 
)

[michael_knight]

Hi @Fowmy thanks for the response, I need to have the relationship inactive. I tried to recreate my main dataset as best as possible, as I'm using multiple date columns with the Date table I needed to have them inactive

Do you know how I'd be able to do the measure you suggested but with the relationship inactive?

[Fowmy · Accepted solution]

@michael_knight 

Do a crossjoin:

Total = 
SUMX(
    CROSSJOIN( VALUES(Agents[Agent]),VALUES('Date'[Month/Year])) ,
    IF([Margin Measure] >=100, 1000, BLANK()) 
)

[michael_knight]

Good stuff, thank you!

Out of curiosity, will this measure work with Switch(True() ?

[Greg_Deckler]

@michael_knight This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907