Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Fátima
Helper II
Helper II

Calculate percentage of answers in a form by question

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

IDFORM_IDANSWER_IDSN_CHECKEDQUESTION_IDCATEGORY_ID
24306140360633
24307140400633
24308140441633
24309140480633
24310140520633
24311140560674
24312140600674
24313140641674
24314141681674
24315140721713

 

ANSWERS

IDQUESTION_IDANSWER_NAME
3663All
4063Most of them
4463Half of them
4863Just a few
5263None
5667Answer 1
6067Answer 2
6467Answer 3
6867Answer 4
7271Yes

 

QUESTIONS

IDQUESTION_NAMECATEGORY_ID
63Question 13
67Question 24
71Question 33

 

CATEGORIES

IDNOMBRE
3CAT_1
4CAT 2

 

FORMS

IDIS_ACTIVE
3TRUE
4FALSE

 

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

 

Captura-MOD.jpg

 

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!

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

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

View solution in original post

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!

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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

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!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.