cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Emilija
Regular Visitor

DAX help with calculating response rate for sorted table

I need assistance to calculate the response rate per person, based on test number, for the following table

Test Number

Name

Device

Response

Test 1

Josh

Mobile

No response

Test 1

Josh

Home

Yes

Test 1

Mike

Mobile

No response

Test 1

Mike

Home

No response

Test 2

Josh

Mobile

Yes

Test 2

Josh

Home

/

Test 2

Mike

Mobile

Yes

Test 2

Mike

Home

/

Test 3

Josh

Mobile

Yes

Test 3

Josh

Home

/

Test 3

Mike

Mobile

No response

Test 3

Mike

Home

Yes

If the person does not pick up their first device, the second device is automatically called. If they do pick up the first

device then the second device is not called(hence "/" symbol).

 

For instance for "test 1" since josh replied on his home phone his overall "pick" up response is yes. Thus his total response rate for all three tests is (3/3) 100%. Mikes response rate is (2/3)66.7%

1 ACCEPTED SOLUTION
Anonymous
Not applicable

[Pick Up Response Rate] =
var __numberOfYeses =
	CALCULATE(
		COUNTROWS( T ),
		T[Response] = "Yes"
	)
var __numberOfTests =
	DISTINCTCOUNT( T[Test Number] )
var __ratio =
	DIVIDE( __numberOfYeses, __numberOfTests )
return
	__ratio

Best

Darek

View solution in original post

1 REPLY 1
Anonymous
Not applicable

[Pick Up Response Rate] =
var __numberOfYeses =
	CALCULATE(
		COUNTROWS( T ),
		T[Response] = "Yes"
	)
var __numberOfTests =
	DISTINCTCOUNT( T[Test Number] )
var __ratio =
	DIVIDE( __numberOfYeses, __numberOfTests )
return
	__ratio

Best

Darek

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors