Percentage of values in column

[JakeSWTC]

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!

[v-juanli-msft]

Hi @JakeSWTC 

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.

[d_gosbell]

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]

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

[d_gosbell]

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 · Accepted solution]

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!