Forum Discussion
Visualize Mulitselection Survey Questions with a Relative Bar Chart
Hello everyone,
I have created a dashboard to visualize complex survey data. This video has helped a lot and shows the kind of question I am dealing with: https://www.youtube.com/watch?v=rI0uHdBwgPY. Essentially, the survey was conducted in two years (20222 and 2023). Each row refers to a person who has answered the survey and has a weight smaller than 1 if this group is overrepresented in the survey and bigger than 1 if it is underrepresented. My problem lies with the multiselection question (last two columns). The raw data has the structure as shown below (short example):
To achieve the data structure shown in the video, I replace the "No" values with "null" and the "Yes" values with either "MS Word" or "Facebook". I than create a new querry with the ID and the last two columns, unpivot the multiselection columns and remove the attribute column:
I then created a bar chart using "App" column from the querry above as y-axis, the survey year from the original querry as the legend, and the sum of weights from the original querry. This results in the following chart with an example slicer for the gender:
This result comes very close to my desired chart. The only problem that I have is that I want relative values, i. e. 59% of the people taking the survey in 2022 use MS Word and 74% use Facebook (in this example the sum of weights coincides with the percentage values because I chose a sample size of 10 per year). Showing the weights as a percentage of the total does not achieve my desired outcome as I don't want percentages of th total sum.
I hope that I could make my problem clear. I am happy to provide you with any further information.
Thank you so much in advance for your time and help.
Hi benekun ,
Depending on the information you have provided, you can follow these steps below:
1.Add new columns.
MS Word = VAR _YEAR = 'Table'[Year] VAR _MSWord = CALCULATE ( COUNT ( 'Table'[App] ), FILTER ( 'Table', 'Table'[Year] = _YEAR && 'Table'[App] = "MS Word" ) ) VAR _ALL = CALCULATE ( COUNT ( 'Table'[App] ), FILTER ( 'Table', 'Table'[Year] = _YEAR ) ) RETURN _MSWord / _ALLFacebook = VAR _YEAR = 'Table'[Year] VAR _Facebook = CALCULATE ( COUNT ( 'Table'[App] ), FILTER ( 'Table', 'Table'[Year] = _YEAR && 'Table'[App] = "Facebook" ) ) VAR _ALL = CALCULATE ( COUNT ( 'Table'[App] ), FILTER ( 'Table', 'Table'[Year] = _YEAR ) ) RETURN _Facebook / _ALL2.Add new measures.
MS = VAR _YEAR = SELECTEDVALUE ( 'Table'[Year] ) RETURN CALCULATE ( MAX ( 'Table'[MS Word] ), FILTER ( 'Table', 'Table'[Year] = _YEAR ) )FB = VAR _YEAR = SELECTEDVALUE ( 'Table'[Year] ) RETURN CALCULATE ( MAX ( 'Table'[Facebook] ), FILTER ( 'Table', 'Table'[Year] = _YEAR ) )3.Put measures in visual.
Final output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Update: I could solve the problem using the quite new visual calculations.
3 Replies
Hi benekun ,
Depending on the information you have provided, you can follow these steps below:
1.Add new columns.
MS Word = VAR _YEAR = 'Table'[Year] VAR _MSWord = CALCULATE ( COUNT ( 'Table'[App] ), FILTER ( 'Table', 'Table'[Year] = _YEAR && 'Table'[App] = "MS Word" ) ) VAR _ALL = CALCULATE ( COUNT ( 'Table'[App] ), FILTER ( 'Table', 'Table'[Year] = _YEAR ) ) RETURN _MSWord / _ALLFacebook = VAR _YEAR = 'Table'[Year] VAR _Facebook = CALCULATE ( COUNT ( 'Table'[App] ), FILTER ( 'Table', 'Table'[Year] = _YEAR && 'Table'[App] = "Facebook" ) ) VAR _ALL = CALCULATE ( COUNT ( 'Table'[App] ), FILTER ( 'Table', 'Table'[Year] = _YEAR ) ) RETURN _Facebook / _ALL2.Add new measures.
MS = VAR _YEAR = SELECTEDVALUE ( 'Table'[Year] ) RETURN CALCULATE ( MAX ( 'Table'[MS Word] ), FILTER ( 'Table', 'Table'[Year] = _YEAR ) )FB = VAR _YEAR = SELECTEDVALUE ( 'Table'[Year] ) RETURN CALCULATE ( MAX ( 'Table'[Facebook] ), FILTER ( 'Table', 'Table'[Year] = _YEAR ) )3.Put measures in visual.
Final output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Ada,
first of all, I want to thank you for your very detailled response. That brings me closer to my desired solution.
To make it easier, I put the sample data as well as the desired diagramm including calculations in this google sheet: https://docs.google.com/spreadsheets/d/1C5q65OKPGnXgYdTMAudUhqZLo2Odk2K60kAiXqNMOA8/edit?usp=sharing
Ideally, the diagramm would look like this (grouped by application):
Another issue that I am facing is that I cannot simply count the respective rows and calculate the percentages accordingly. Since this is weighted data, I have to sum the weights according to the answer options for each year and divide it by the non-NA sum per year. Using excel for 2022: =SUMIFS(Data!$E:$E;Data!$B:$B;2022;Data!$F:$F;"Yes")/SUMIFS(Data!$E:$E;Data!$B:$B;2022;Data!$F:$F;"<>NA")
I hope that clarified things. Unfortunately, I was not able to adapt your Power BI suggestions accordingly. I hope the excel example is sufficient.
Thank you again for your time.
Best regards,
Benedikt FranzUpdate: I could solve the problem using the quite new visual calculations.