Summarizing values in measure with IF statement

[sp91]

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?

[v-yanjiang-msft · Accepted solution]

Hi @sp91 ,

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.

[tamerj1]

Hi @sp91 

can you paste a screenshot of your source data table?

[sp91]

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]

Hi @sp91 

you can edit your measure

SUMX (

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

CALCULATE ( your original formula)

)