Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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