Calculate percentage of answers in a form by question

[Fátima]

Hi!

I am not sure how much info is going to be needed to answer my question, maybe not as much, but just in case I'll try to explain the current situation and what I'm trying to achieve. The question is at the end.

I have a dataset that represents the answers given to several forms as follows:

FORM_ANSWERS

ID	FORM_ID	ANSWER_ID	SN_CHECKED	QUESTION_ID	CATEGORY_ID
24306	140	36	0	63	3
24307	140	40	0	63	3
24308	140	44	1	63	3
24309	140	48	0	63	3
24310	140	52	0	63	3
24311	140	56	0	67	4
24312	140	60	0	67	4
24313	140	64	1	67	4
24314	141	68	1	67	4
24315	140	72	1	71	3

ANSWERS

ID	QUESTION_ID	ANSWER_NAME
36	63	All
40	63	Most of them
44	63	Half of them
48	63	Just a few
52	63	None
56	67	Answer 1
60	67	Answer 2
64	67	Answer 3
68	67	Answer 4
72	71	Yes

QUESTIONS

ID	QUESTION_NAME	CATEGORY_ID
63	Question 1	3
67	Question 2	4
71	Question 3	3

CATEGORIES

ID	NOMBRE
3	CAT_1
4	CAT 2

FORMS

ID	IS_ACTIVE
3	TRUE
4	FALSE

I have made a stacked bar chart that shows the questions in the category selected in a filter, and the bars represent the number of answers that have been selected (SN_CHECKED=TRUE) to each answer of each question, just counting the ones of the forms that are active (IS_ACTIVE=TRUE):

I need to add as additional information the percentage of each answer over all the answers of the question. For example, on the one selected on the image, if there are 118 people that selected this answer over the 700 people that answered this question, this answer has been selected by 16,86% of the people.

My question is: how can I calculate this percentage? I have tried modifying some answers given in a similar questions on this forum, but I don't fully understand the DAX code they are using so it doesn't work properly, that's why the percentage showing is incorrect. The measure I have right now is as follow:

percentage=

DIVIDE(CALCULATE(COUNTROWS(FORMS_ANSWERS),FORMS_ANSWERS[SN_CHECKED]==TRUE(),FORMS[IS_ACTIVE]==true),CALCULATE(CALCULATE(COUNTROWS(FORMS_ANSWERS),FORMS_ANSWERS[SN_CHECKED]==TRUE(),FORMS[IS_ACTIVE]==true),ALLEXCEPT(FORMS_ANSWERS,FORMS_ANSWERS[QUESTION_ID])),0)

If you need some more information, please ask me!

Thanks in advance!

Best regards!

[amitchandak · Accepted solution]

@Fátima , try like

DIVIDE(CALCULATE(COUNTROWS(FORMS_ANSWERS),FORMS_ANSWERS[SN_CHECKED]==TRUE(),FORMS[IS_ACTIVE]==true() ),CALCULATE(CALCULATE(COUNTROWS(FORMS_ANSWERS),FORMS_ANSWERS[SN_CHECKED]==TRUE(),FORMS[IS_ACTIVE]==true),removefilters(ANSWERS,ANSWERS[ANSWERS_NAME])),0)

[amitchandak · Accepted solution]

@Fátima , try like

DIVIDE(CALCULATE(COUNTROWS(FORMS_ANSWERS),FORMS_ANSWERS[SN_CHECKED]==TRUE(),FORMS[IS_ACTIVE]==true() ),CALCULATE(CALCULATE(COUNTROWS(FORMS_ANSWERS),FORMS_ANSWERS[SN_CHECKED]==TRUE(),FORMS[IS_ACTIVE]==true),removefilters(ANSWERS,ANSWERS[ANSWERS_NAME])),0)

[Fátima · Accepted solution]

Hi @amitchandak !

Thank you very much for your answer!

At first, it threw an error saying that multiple table arguments are not allowed in the function funcion ALL/ALLNOBLANKROW/REMOVEFILTERS, but if I leave it like this it works perfectly!

DIVIDE(CALCULATE(COUNTROWS(FORMS_ANSWERS),FORMS_ANSWERS[SN_CHECKED]==TRUE(),FORMS[IS_ACTIVE]==true() ),CALCULATE(CALCULATE(COUNTROWS(FORMS_ANSWERS),FORMS_ANSWERS[SN_CHECKED]==TRUE(),FORMS[IS_ACTIVE]==true),removefilters(ANSWERS[ANSWERS_NAME])),0)

Regards!