Visualize Mulitselection Survey Questions with a Relative Bar Chart

[benekun]

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.

[v-yifanw-msft · Accepted solution]

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 / _ALL

Facebook = 
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 / _ALL

2.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.

[benekun]

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 Franz

[benekun · Accepted solution]

Update: I could solve the problem using the quite new visual calculations.

[v-yifanw-msft · Accepted solution]

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 / _ALL

Facebook = 
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 / _ALL

2.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.