cancel
Showing results for
Did you mean:
JakeSWTC Frequent Visitor

Percentage of values in column

Hi,

I'm brand new to Power BI and have a question.  I have a column of text values that to find the average of for certain values.  The options for the column are:

• Very satisfied
• Satisfied
• Neutral
• Dissatisfied
• Very dissatisfied
• N/A

I want the average to calculate all the "Very satisfied" and "Satisfied" out of all of them excluding "N/A".

How can I accomplish this?  I can only figure out how to get it to count all of the value.  Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
JakeSWTC Frequent Visitor

Re: Percentage of values in column

Thank you both for your help.  I got it working following the suggestion of @v-juanli-msft.

I ended up creating 3 measures.

I did:

_Overall Quality Satisfied =
CALCULATE(
COUNTA('Satisfacation Survey'[overall quality of_x]),
'Satisfacation Survey'[overall quality of_x] IN { "Very satisfied", "Satisfied"}
)

and

_Overall Quality Total =
CALCULATE(
COUNTA('Satisfacation Survey'[overall quality of_x]),
'Satisfacation Survey'[overall quality of_x] IN { "Very satisfied", "Satisfied","Neutral", "Dissatisfied", "Very Dissatisfied" }
)

and then

_Overall Quality Percent = ([_Overall Quality Satisfied] / [_Overall Quality Total])
Appreciate the help!
5 REPLIES 5 Super User

Re: Percentage of values in column

It's hard to be sure without knowing the structure of your model, but something like the following should work. (Note: you'll need to replace the "table1" references with the name of your table)

Pcnt =

var _satRows = CALCULATE( COUNTROWS( VALUES( table1[ability of help_x002]) )
, table1[ability of help_x002] IN { "Satisfied", "Very satisfied" } )
var _allRows = COUNTROWS( VALUES( table1[ability of help_x002]) )
return DIVIDE( _satRows, _allRows)

JakeSWTC Frequent Visitor

Re: Percentage of values in column

Thank you for the response.  When I add that measure and set it the "Value" I am still getting a 100%. (It should be something like 97%).

Did I do something wrong?  I did put in my table name (Satisfaction Survey).  Community Support Team

Re: Percentage of values in column

Based on my udnerstanding,

"I want the average to calculate all the "Very satisfied" and "Satisfied" out of all of them excluding "N/A""

this sentence can be transformed to the following formula:

(count rows of "Very satisfied" and "Satisfied")/(count all rows excluding "N/A")

If i understand you right,

create measures

Measure =
CALCULATE(COUNT(Sheet1[column]),FILTER(ALL(Sheet1),[column]="Very satisfied"||[column]="satisfied"))

Measure 2 =
CALCULATE(COUNT(Sheet1[column]),FILTER(ALL(Sheet1),[column]<>"N/A"))

Measure 3 = [Measure]/[Measure 2] Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Super User

Re: Percentage of values in column

I'm not sure why this would not be working for you. Can you paste in the actual expression you used?

ps. I noticed that I forgo the exclude N/A condition

Pcnt =

var _satRows = CALCULATE( COUNTROWS( VALUES( 'Satisfaction Survey'[ability of help_x002]) )
, 'Satisfaction Survey'[ability of help_x002] IN { "Satisfied", "Very satisfied" } )
var _allRows = CALCULATE( COUNTROWS( VALUES( table1[ability of help_x002]) ), 'Satisfaction Survey'[ability of help_x002] <> "N/A")
return DIVIDE( _satRows, _allRows)

Or did the approach suggested by @v-juanli-msft work for you?

JakeSWTC Frequent Visitor

Re: Percentage of values in column

Thank you both for your help.  I got it working following the suggestion of @v-juanli-msft.

I ended up creating 3 measures.

I did:

_Overall Quality Satisfied =
CALCULATE(
COUNTA('Satisfacation Survey'[overall quality of_x]),
'Satisfacation Survey'[overall quality of_x] IN { "Very satisfied", "Satisfied"}
)

and

_Overall Quality Total =
CALCULATE(
COUNTA('Satisfacation Survey'[overall quality of_x]),
'Satisfacation Survey'[overall quality of_x] IN { "Very satisfied", "Satisfied","Neutral", "Dissatisfied", "Very Dissatisfied" }
)

and then

_Overall Quality Percent = ([_Overall Quality Satisfied] / [_Overall Quality Total])
Appreciate the help!

Announcements Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Top Kudoed Authors
Users Online
Currently online: 122 members 1,674 guests
Recent signins:
• MolitorCM • martinhyy • LuisP • • emmetk • gowtham_raj_n • vyogi • osyed • Abdul_Azlin • andrewmelder • retailbusiness • Veronica8481 • data_insights • jmalpass 