topic Re: Create new table with percent of respondents for each category in DAX Commands and Tips

Create new table with percent of respondents for each category

klontok — Wed, 07 Feb 2024 15:49:21 GMT

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])

)

Re: Create new table with percent of respondents for each category

lbendlin — Thu, 08 Feb 2024 00:01:37 GMT

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

Re: Create new table with percent of respondents for each category

Anonymous — Thu, 08 Feb 2024 08:23:04 GMT

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.

Re: Create new table with percent of respondents for each category

klontok — Thu, 08 Feb 2024 17:54:24 GMT

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.