Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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?

 

sp91_0-1653672334870.png

sp91_0-1653672552385.png

 

 

 

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

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

vkalyjmsft_0-1654149251344.png

Here's my solution, create a measure.

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

Get the correct result.

vkalyjmsft_1-1654149336718.png

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.

View solution in original post

4 REPLIES 4
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

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

vkalyjmsft_0-1654149251344.png

Here's my solution, create a measure.

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

Get the correct result.

vkalyjmsft_1-1654149336718.png

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
Super User
Super User

Hi @Anonymous 

can you paste a screenshot of your source data table?

Anonymous
Not applicable

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.

 

sp91_0-1653676727141.png

 

Hi @Anonymous 

you can edit your measure

SUMX (

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

CALCULATE ( your original formula)

)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.