Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to 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:
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):
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!
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:
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):
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!
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?
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?
Proud to be a Super User!
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
84 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
132 | |
110 | |
64 | |
55 |