cancel
Showing results for
Did you mean:

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

Frequent Visitor

## Calculating response rate for multiple entities

So have have the following survey data:

I know the number of possible respondents for each company but that data is not in the table, so how can I calcuale the response rate?

1 ACCEPTED SOLUTION
Resident Rockstar

If the data is in a different table, then you can add a relationship between Company name columns of both tables. Do make sure you have unique Companynames and every name only occurs once in the second table.

Once you have that relationship, you can calculate it by creating a measure or calculated column in the second table (the table with the amount of surveys sent out per company).

I've recreated the situation with the following tables:

Table 1 - Survey responses per respondee which captures also the company of the respondee.Table 2: ALl companies with total of sent out surveys

PowerBI recognizes de relationship immediately; a 1-to-many relationship between the Company column.

We are now going to add a calculated column to the SurveysSent table to calculate the response rate per company. The formula is

``ResponseRate = COUNTROWS(RELATEDTABLE(Responses))/SurveysSent[Sent]``

The rows in table Responses are filtered because I used the RELATEDTABLE expression; it returns only the applicable rows in the related table. In this case, because this is evaluated for every row in SurveysSent, it filters on the company name. This result in the following table (from Data view):

Table with an additional calculated column

You could also create a dimension in a similar fashion, but this does the trick as well. The benefit of using a measure is that you can use slices in your report to filter out specific respones in calculating your response rate.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Kudo's are welcome 🙂

Proud to be a Super User!

7 REPLIES 7
Resident Rockstar

If the data is in a different table, then you can add a relationship between Company name columns of both tables. Do make sure you have unique Companynames and every name only occurs once in the second table.

Once you have that relationship, you can calculate it by creating a measure or calculated column in the second table (the table with the amount of surveys sent out per company).

I've recreated the situation with the following tables:

Table 1 - Survey responses per respondee which captures also the company of the respondee.Table 2: ALl companies with total of sent out surveys

PowerBI recognizes de relationship immediately; a 1-to-many relationship between the Company column.

We are now going to add a calculated column to the SurveysSent table to calculate the response rate per company. The formula is

``ResponseRate = COUNTROWS(RELATEDTABLE(Responses))/SurveysSent[Sent]``

The rows in table Responses are filtered because I used the RELATEDTABLE expression; it returns only the applicable rows in the related table. In this case, because this is evaluated for every row in SurveysSent, it filters on the company name. This result in the following table (from Data view):

Table with an additional calculated column

You could also create a dimension in a similar fashion, but this does the trick as well. The benefit of using a measure is that you can use slices in your report to filter out specific respones in calculating your response rate.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Kudo's are welcome 🙂

Proud to be a Super User!

Frequent Visitor

Thanks that worked!

Frequent Visitor

Thanks for the solution, and this would totally work. However, I would somehow need to create that first table (Table 1 - Survey responses per respondee which captures also the company of the respondee.), and I am unsure how to do it. Any suggestions?

Resident Rockstar
That first table is the exact table you gave us, without department name as that column doesn't impact the solution asked. You already had that table? I created a second table as you mentioned you did know the total amount of surveys sent but it wasn't in the first table, but in your case you would have to load that data into your report.

Proud to be a Super User!

Frequent Visitor

Ah I see whant you sare saying. So while I have the total number of surveys sent out per company, how woud I "load this data" into my report?

Resident Rockstar
Well that depends on where the data is coming from. You could put it in an Excel spreadsheet by hand and load it as a another query. Or you can manually create the table and populate it by hand by pressing the 'Enter data' in the report view.

Proud to be a Super User!

Frequent Visitor

Okay, So I created a new table with the follwing data:

Here is my model tab:

The orignal data in Excel. How can I create the relationship? Do I still need to create the 1st table?

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors