Forum Discussion

nix70's avatar
nix70
Frequent Visitor
3 years ago
Solved

Bar chart legend hierarchy

Hello,

 

I have an anonymised list of people who were involved in a survey - there is the population of everyone who could have been surveyed, the invitees, and the respondees. The respondees are a subset of the invitees who are a subset of the population. I am trying to show the demographics of each group, in this case gender.

 

One thing I was able to do is categorise everyone into a group of respondees, a group of invitees who didn't respond, and a group of the population who weren't invited. However, this approach means the gender distribution of the population on my chart is actually excluding all invitees.

 

This is a summary of the data and how I want the graph in Power BI to appear, figure-wise:

 

 

Here is how the chart appears in Power BI with the exclusions

 

My question is how can I get the graph to show the gender distribution for whole population vs whole invitees vs respondees? I've attached the files below. If it helps, I used this formula to calculate the percentage of each group:

 

Pct Group = SUM('GenderSurvey'[One])/(CALCULATE(SUM('GenderSurvey'[One]),FILTER(ALL('GenderSurvey'),'GenderSurvey'[Profile]=SELECTEDVALUE('GenderSurvey'[Profile]))))
 

GenderSurvey.pbix

Surveyeg.xlsx

 

Hope this makes sense!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi nix70 ,

     

    According to your statement, I have a test on your sample.

    Try measure as below.

    New Pct Group = 
VAR _POP =
    DIVIDE (
        CALCULATE (
            COUNT ( GenderSurvey[ID] ),
            ALLEXCEPT ( GenderSurvey, GenderSurvey[Gender] )
        ),
        CALCULATE ( COUNT ( GenderSurvey[ID] ), ALLSELECTED ( GenderSurvey ) )
    )
VAR _INV =
    DIVIDE (
        CALCULATE (
            COUNT ( GenderSurvey[ID] ),
            ALLEXCEPT ( GenderSurvey, GenderSurvey[Gender] ),
            GenderSurvey[Profile] IN { "Invited", "Responded" }
        ),
        CALCULATE (
            COUNT ( GenderSurvey[ID] ),
            ALLSELECTED ( GenderSurvey ),
            GenderSurvey[Profile] IN { "Invited", "Responded" }
        )
    )
VAR _RES =
    DIVIDE (
        CALCULATE (
            COUNT ( GenderSurvey[ID] ),
            ALLEXCEPT ( GenderSurvey, GenderSurvey[Gender] ),
            GenderSurvey[Profile] = "Responded"
        ),
        CALCULATE (
            COUNT ( GenderSurvey[ID] ),
            ALLSELECTED ( GenderSurvey ),
            GenderSurvey[Profile] = "Responded"
        )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( GenderSurvey[Profile] ),
        "aPopulation", _POP,
        "Invited", _INV,
        "Responded", _Res
    )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi nix70 ,

     

    According to your statement, I have a test on your sample.

    Try measure as below.

    New Pct Group = 
VAR _POP =
    DIVIDE (
        CALCULATE (
            COUNT ( GenderSurvey[ID] ),
            ALLEXCEPT ( GenderSurvey, GenderSurvey[Gender] )
        ),
        CALCULATE ( COUNT ( GenderSurvey[ID] ), ALLSELECTED ( GenderSurvey ) )
    )
VAR _INV =
    DIVIDE (
        CALCULATE (
            COUNT ( GenderSurvey[ID] ),
            ALLEXCEPT ( GenderSurvey, GenderSurvey[Gender] ),
            GenderSurvey[Profile] IN { "Invited", "Responded" }
        ),
        CALCULATE (
            COUNT ( GenderSurvey[ID] ),
            ALLSELECTED ( GenderSurvey ),
            GenderSurvey[Profile] IN { "Invited", "Responded" }
        )
    )
VAR _RES =
    DIVIDE (
        CALCULATE (
            COUNT ( GenderSurvey[ID] ),
            ALLEXCEPT ( GenderSurvey, GenderSurvey[Gender] ),
            GenderSurvey[Profile] = "Responded"
        ),
        CALCULATE (
            COUNT ( GenderSurvey[ID] ),
            ALLSELECTED ( GenderSurvey ),
            GenderSurvey[Profile] = "Responded"
        )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( GenderSurvey[Profile] ),
        "aPopulation", _POP,
        "Invited", _INV,
        "Responded", _Res
    )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • nix70's avatar
      nix70
      Frequent Visitor
      3 years ago

      Wow Rico, that's brilliant and does exactly what I wanted it to do. Thank you for your help!