Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Summarizing values in measure with IF statement

Hello,

 

Please see below. I have a dataset that shows manager name, measure (T_70_75) that puts 1 if score between 70-75, else 0, and the year/month of the score (1st screenshot below). I want to sum the the 1's across all months (2nd screenshot), so if a manager had a score of 70-75 for 2 months, sum to 2 for each manager, and so on. How can I do this?

 

 

 

 

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, I create a sample, T_70_75 and Score are measures like yours.

    Here's my solution, create a measure.

    Total Month at 70-75 = COUNTROWS(FILTER('Data','Data'[T_70_75]=1))

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 

    can you paste a screenshot of your source data table?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Sure,  see below. Score is the numerator divided by denominator. I then created a measure called 'T_70_75' (prior screenshot from the first post) that is an if statement, if between 70-75 put 1, else 0. I want to count the number of 1's for each manager having that score range for each month period.

       

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Hi Anonymous 

        you can edit your measure

        SUMX (

        SUMMARIZE ( TableName, TableName[Manager], TableName[Year Month Number] ),

        CALCULATE ( your original formula)

        )

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    According to your description, I create a sample, T_70_75 and Score are measures like yours.

    Here's my solution, create a measure.

    Total Month at 70-75 = COUNTROWS(FILTER('Data','Data'[T_70_75]=1))

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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