Bar chart legend hierarchy

[nix70]

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!

[v-rzhou-msft · Accepted solution]

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]

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