Calculating response rate for multiple entities

[cmajewski]

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?

[JarroVGIT · Accepted solution]

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 🙂

[cmajewski]

Thanks that worked!

[cmajewski]

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?

[JarroVGIT]

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.

[cmajewski]

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? 

[JarroVGIT]

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.
How did you get your original dataset loaded into Power Bi?

[cmajewski]

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?