How to SUM a measure that has variables

[Traciicolliins]

Why does this not show the proper total?

Gained ACS KPI =

VAR _thisyear = [yDrink ACS Dynamic TY]

VAR _lastyear = [yDrink ACS Dynamic LY]

return

if(and(_thisyear>0,_lastyear=0),1,0)

when i put this in a table, the flag works (correctly flagging accounts that are gained (ACS TY=1, ACS LY=0) but the sum doesn't work

[Greg_Deckler · Accepted solution]

@Traciicolliins First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

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

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[v-zhouwen-msft · Accepted solution]

Hi Greg_Deckler ,thanks for the quick reply, I'll add more.

Hi @Traciicolliins ,

Regarding your problem, you need to create a dummy table to store the results.

Something like this

Measure 4 = 
var _table = ADDCOLUMNS(SUMMARIZE('Table','Table'[ID]),"Column",[Measure 3])
RETURN 
IF(NOT ISBLANK(SELECTEDVALUE('Table'[ID])),[Measure 3],SUMX(_table,[Column]))

Best Regards,
Wenbin Zhou

[Greg_Deckler · Accepted solution]

@Traciicolliins First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

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

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8