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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.