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
cmajewski
Frequent Visitor

Calculating response rate for multiple entities

So have have the following survey data:

 

BI.png

 

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
JarroVGIT
Resident Rockstar
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 1 - Survey responses per respondee which captures also the company of the respondee.Table 2: ALl companies with total of sent out surveysTable 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 columnTable 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 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
JarroVGIT
Resident Rockstar
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 1 - Survey responses per respondee which captures also the company of the respondee.Table 2: ALl companies with total of sent out surveysTable 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 columnTable 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 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks that worked!

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?

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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? 

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?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Table2.png

 

Here is my model tab:

Relationship.png

 

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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