Forum Discussion
Post ProdigyAverage for Survey Result
Dear All,
How can I get the average (%) of the below table in PBI?
I wanted to get how many people are delighted, Very Satisfied, Satisfied & Dissatisfied.
maybe this Measure will satisfy your requirement
Delighted % = DIVIDE( ( CALCULATE(COUNTROWS(Table), Table[Question 1] = "Delighted") + CALCULATE(COUNTROWS(Table), Table[Question 2] = "Delighted") + CALCULATE(COUNTROWS(Table), Table[Question 3] = "Delighted") + CALCULATE(COUNTROWS(Table), Table[Question 4] = "Delighted") ) , 4 * CALCULATE(COUNTROWS(Table), ALL(Table) ) )pick your new measures in the right pane, go to Measure Tools Ribbon and press "%" button
12 Replies
- amitchandak
Super User
gauravnarchal , Better you Unpivot this table first. Then it will easy
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/
gauravnarchalamitchandak Is there any other better option instead of unpivot?
Can a measure {Average formula} handle this?
- gauravnarchal
Is there any other better option instead of unpivot?
Can a measure {Average formula} handle this?
- az38
Community Champion
whats exactly % do you need? by each question separately or by all amoun of data?
for example you can create a few measures like
Question 1 Delighted % = DIVIDE( CALCULATE(COUNTROWS(Table), Table[Question 1] = "Delighted") , CALCULATE(COUNTROWS(Table), ALL(Table) ) )- gauravnarchal
az38 I want the % by all amount of data
- az38
maybe this Measure will satisfy your requirement
Delighted % = DIVIDE( ( CALCULATE(COUNTROWS(Table), Table[Question 1] = "Delighted") + CALCULATE(COUNTROWS(Table), Table[Question 2] = "Delighted") + CALCULATE(COUNTROWS(Table), Table[Question 3] = "Delighted") + CALCULATE(COUNTROWS(Table), Table[Question 4] = "Delighted") ) , 4 * CALCULATE(COUNTROWS(Table), ALL(Table) ) )- gauravnarchal
az38 - It worked. The result is reflecting as 0.36 instead of 36%. How can I change the format to show in percentage?
