Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello!
I have a survey question which is answered on a 1-10 scale, which makes it numeric. However, there is also the answer option 'did not attend', which makes it non-numeric (text). The data type in PowerBI is now text, but I want to make it fixed number so that I can show the average answer (number) in my dashboard. How can I do that while maintaining the 'did not attend' answers? Because if I simply change it to fixed number, I get errors where previously 'did not attend' was.
Thanks! 🙂
maybe you can replace BLANK() with 0 in the formula provided and add a filter on the filter pane of the reporting page to exclude all "did not attend" rows. (if not it will false the amount of your average). Hope it helps
Thank you @Alex87 . This still doesn't work. I get the same message. Can it be that I should not use "" around did not attend in the formula?
Hello @BrittH ,
I built a dummy dataset as follows:
The answer column is typed: Text
I created a new calculated column
SurveyNumeric =
SWITCH(
TRUE(),
[Answer] = "did not attend", BLANK(),
VALUE([Answer])
)
now my table looks like this:
I created a DAX measure:
AverageSurveyResponse = AVERAGE([SurveyNumeric])
I then created Table visual using Survey Question & the created measure
So, it is working. Did you do something different then the described steps mentioned above?
Best regards
Hi @Alex87 ,
Thank you for your effort. The difference between your data and my data is that I only have one column. Let's name it 'session 1'. Then all the cells in that column are either a number (the rating respondents give that session) or 'did not attend'.
I have solved this issue with a workaround. I created another column in Excel and called it 'session 1 without did not attend'. In that column, I emptied all the did not attend cells. With that column, I can show the average rating in PowerBI.
Sorry Brit, I do not understand. I created in the dataset 1 column only as well. That column contains both numeric and text values (column "Answer"). I then create in DAX a calculated column and I finalize with a DAX measure. No workaround is needed.
Can you please mock up for me a sample example please?
1) Ensure Data is Text in Power Query
2) Create a Calculated Column in DAX :
SurveyNumeric =
SWITCH(
TRUE(),
[SurveyResponse] = "did not attend", BLANK(),
VALUE([SurveyResponse])
)
3) Create Measures for Average Calculation in a visualization
AverageSurveyResponse = AVERAGE([SurveyNumeric])
Thank you @Alex87 ! When I create the calculated column I get this error message: cannot convert value "" of type Text to type Number.
User | Count |
---|---|
73 | |
70 | |
38 | |
25 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
43 | |
42 |