Forum Discussion

benekun's avatar
benekun
Frequent Visitor
2 years ago
Solved

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=rI0uHdBw...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    benekun
    2 years ago

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

benekun's avatar
benekun
Frequent Visitor
2 years ago

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's avatar
benekun
Frequent Visitor
2 years ago

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