Create new table with percent of respondents for each category

[klontok]

I have an unpivoted table of survey data where respondents could choose more than one of several options.

Survey ID	Infection	Response
0001	Sinusitis	Selected
0001	Sepsis	Selected
0002	Pneumonia	Selected
0003	Sepsis	Selected
0003	Pneumonia	Selected

I would like to create a table that shows the percent of respondents that chose each infection.

Infection	% of respondents
Sinusitis	33%
Sepsis	66%
Pneumonia	66%

I tried using SUMMARIZE, but I can't seem to get the correct denominator. The COUNTDISTINCT below counts only the distinct Survey IDs within the infection category instead of in the entire table, so I get 100% for every type of infection. Maybe SUMMARIZE is the wrong way to go? Thanks in advance for any help! 

Table = SUMMARIZE('Infections Prior to Diagnosis', 'Infections Prior to Diagnosis'[Infections],"Percent of respondents",DIVIDE(COUNT('Infections Prior to Diagnosis'[Response]),COUNTDISTINCT('Infections Prior to Diagnosis'[Survey ID])

)

[lbendlin · Accepted solution]

This question is much more complex than you might think, but here is a simplistic approach that takes some liberties.

[v-kongfanf-msft · Accepted solution]

Hi @klontok ,

If you want to create a calculation table, I have made a modification on the formula you provided, refer below:

Table_ = 
SUMMARIZE(
    'Infections Prior to Diagnosis',
    'Infections Prior to Diagnosis'[Infection],
    "Percent of respondents",
    DIVIDE(
        CALCULATE (
            COUNTROWS('Infections Prior to Diagnosis'),
            FILTER(
                ALL('Infections Prior to Diagnosis'),
                'Infections Prior to Diagnosis'[Infection] = VALUES('Infections Prior to Diagnosis'[Infection])
            )
        ),
        CALCULATE(
            DISTINCTCOUNT('Infections Prior to Diagnosis'[Survey ID]),
            ALL('Infections Prior to Diagnosis')
        )
    )
)

Best Regards,
Adamk Kong

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

[lbendlin · Accepted solution]

This question is much more complex than you might think, but here is a simplistic approach that takes some liberties.

[klontok]

Thank you so much! I was assuming I had to create another table, but the measure actually works better for my purposes because the slicers filter it.