Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have finally taken the plunge and decided to use PowerBI for analysing my survey data, instead of the specialised survey analysis software.
All is very straightforward, except when dealing with multi-answer questions. These are questions where the respondent can select more than answer (for example, "Where did you see this advert" with possible answers TV, Radio, Newspaper, Magazine).
The data structure of such questions looks something like this:
Case | B01_TV | B01_Radio | B01_Newsp | B01_Mag | B01_None |
1 | 1 | 1 | |||
2 | 1 | 1 | 1 | ||
3 | 1 | ||||
4 | 1 | ||||
... |
What I need is to combine these so that when I plot a bar chart for example, I'll be able to rank the popularity of each medium (so sum of 1s in each column divided by the total number of Cases).
I can think of a couple of ways of achieving this, but they are very very long-winded. Does anyone know of a smart and quick way? (A typical survey can have 10+ such questions which is why I'm looking for the most efficient way.)
Many thanks in advance!
George.
Solved! Go to Solution.
Hi @Anonymous,
It seems that the Unpivot option may be the best solution in this case.
This would work if these were the only data I had in the table, but I have another 30 variables or so, some of which are multi-answers like the one I described. If I unpivot all of them, the table becomes a mess and it's impossible to make any sensible calculations with the other variables.
Is it possible for you to duplicate the table, remove unnecessary columns, then unpivot the duplicated table in Query Editor? By this way, you should be able to create a new table with multi questions which is similar to create a calculate table using DAX.
Regards
I suggest you unpivot the data to this shape
case. Answer
1. Tv
1. Radio
2. Radio
2. News
3. Mag
you don't need the 1s.
You can then do a distinct count on the answer to see how many of each.
Distinct count on Case to count the cases
divide the 2 to get an average etc
Thank you @MattAllington
This would work if these were the only data I had in the table, but I have another 30 variables or so, some of which are multi-answers like the one I described. If I unpivot all of them, the table becomes a mess and it's impossible to make any sensible calculations with the other variables.
I thought of creating a calculated table with the multi questions, but I'm not able to unpivot the columns...
Hi @Anonymous,
It seems that the Unpivot option may be the best solution in this case.
This would work if these were the only data I had in the table, but I have another 30 variables or so, some of which are multi-answers like the one I described. If I unpivot all of them, the table becomes a mess and it's impossible to make any sensible calculations with the other variables.
Is it possible for you to duplicate the table, remove unnecessary columns, then unpivot the duplicated table in Query Editor? By this way, you should be able to create a new table with multi questions which is similar to create a calculate table using DAX.
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |