Multiple choice with Net count overcodes

[elaj]

Hi,

I have a new problem.

I am trying to visualize a question where a respondent could have multiple answers for multiple brands.

I have found solutions, but they seem to me very complicated and are actually not really practical.

Additionally I would like to summarize answers as a net count. which is also apparently not easily possible.

1.
Since I have to pivot all answers, and thus multiply the respondents by the number of possible answers, I have a problem with the base for percentage.
I solved this problem by making a base table and additionally a pivotized table with the multiplied respondents. Then I matched them via the model view. This seems to me to be very cumbersome. Is there another possibility?

2.
When I try to create overcodes, I always get the gross count of the data in the matrix ... which is logical. This is a pity, because I would like to use the functionality of drilling.
Now I have summarized the overcodes as an additional "answer" and so I get the correct values. Unfortunately I cannot use a drill anymore.
Is there a possibility to display the overcodes as a net count and still have the possibility to drill?

I want to have the ability to expand or close the Overcodes. But i dont want the sums to be a gross count.. but a net count.

like that:

pbix attached

Thank you 🙂

[ryan_mayu · Accepted solution]

@elaj 

try this measure

Q N = CALCULATE(DISTINCTCOUNT(data_answers[ID_match]), data_answers[Value] = "quoted")

[elaj]

@ryan_mayu 

Oh yes thank you.. i think this is solving my overcode problem.

Do you have an idea for my base problem too?

I now have the base and the answers in two different tables. And this is getting quite messy when i have more of those data to visualize. Is there a way to do this more easily?

Thanks

[ryan_mayu]

@elaj 

i think you need to improve the data model.

in my opinion, you do not need to have both answer and answer_oc table.

Create a column in OC table

ITEM OC = SWITCH(data_answers_OC[Item],"OC_1-2","1 AND 2","oc_3-4-5","3 AND 4 AND 5",data_answers_OC[Item])

Then you only need to create one group of measures.

[elaj]

I now found out how to construct my base.

it is with the same trick. I just use distinctcount on my ID. So it only counts the respondend one time.

thanks 🙂