How to Sum a Measure with an IF statement

[Garbo62]

Hi

I have a data set of Location where sometimes there is duplicate data for that location, it want to sum location if there are Utilised of Not utilised (ie count hpw may locations are utilised)

I written a measure to return if utilised or Not (1 or 0)

Utilised Activity Sum = ((IF(Sum(BI_SurveyData[RActivitiesUtilised])>1,1,0)))

Column 1 contains duplicate values and what i want to do is sum the above measue , colume 2

[v-yangliu-msft · Accepted solution]

Thanks for the reply from MFelix , please allow me to provide another insight: 
Hi  @Garbo62 ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Test =
var _table1=
SUMMARIZE(
    ALL('BI_SurveyData1'),[Accordant Suvery No],[TimeStart],"Count",[Utilised Activity Sum])
return
COUNTX(
    FILTER(_table1,[Count]>0),[Accordant Suvery No])

2. Result:

If the results above don't meet your expectations, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[MFelix]

Hi @Garbo62 ,

For this you need to use a SUMX function depending on the level of detail should be something similar to:

SUMX(VALUES(TABLE[Column]), [Utilised Activity Sum])

The Table Column should be replaced by the detail level you want to sum it