I have survey data with ratings on a 1-5 scale. I would like to calculate an overall average of this data (ie: 4.5) in PowerBI Desktop but can't figure out how. I would like to have this average recalculate when I apply filters (ie: average rating by survey type). Blank cells should be excluded from the average (not interpreted as zero).
I've attached my data (in Excel) and also included a snapshot of the layout below. Is this best accomplished with measure(s) or a calculated column?
| Survey Type | Extent of Event Assistance | Event Quality | Meeting Quality | Likelihood of Future Participation |
| Trade Show Assistance Program Survey | 4 | 4 | ||
| Trade Show Assistance Program Survey | 3 | 4 | ||
| International Travel Survey | 4 | 5 | 5 | 5 |
| International Travel Survey | 4 | 3 | 5 | |
| International Travel Survey | 5 | 5 | 5 | 5 |
| International Travel Survey | 4 | 5 | 5 | |
| International Travel Survey | 4 | 4 | 4 | 4 |
Hi Mary,
I'm not clear on whether you want to calculate an average across the criteria, the total table or the average of one criteria (e.g. even quality) for a survey type.
Please provide some examples and the expected results based on the above data so we can ensure the solution we offer provides the desired outcome.
Thanks,
Mynda
Sure! I would like to calculate an average across the total table.
Example 1: Average rating (average of all four columns) for all the travel surveys. When I do this in Excel, I get 4.33 as the average rating.
Example 2: Average rating (average of all four columns) for all the Trade Show Assistance Program Surveys. I got 4.57 as the resulting average in Excel.
Hi Mary,
Ideally your data should be in a tabular format for this. i.e. the survey question should be in one column and all the scores in another. So your table would have the following columns:
- Survey ID
- Survey Type
- Survey Question
- Survey Score
You could use Power Query to unpivot the data, which would mean a much simpler DAX formula to calculate the average.
Anyhow, in the attached file you'll find the measure:
=DIVIDE( CALCULATE( SUMX(Table1, [Event Quality]+[Extent of Event Assistance]+[Likelihood of Future Participation]+[Meeting Quality])), ( COUNT([Event Quality]) + COUNT([Extent of Event Assistance]) + COUNT([Likelihood of Future Participation]) + COUNT([Meeting Quality])) )
Hope that helps.
Mynda
Thanks so much! I will play around with unpivoting as well.
