Forum Discussion

Garbo62's avatar
Garbo62
Resolver I
2 years ago
Solved

How to Sum a Measure with an IF statement

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
 

 

 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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