cancel
Showing results for
Did you mean:
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
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)

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?

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.

#### Power Platform Summit North America

Register by September 5 to save \$200

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 122 members 1,674 guests
Recent signins: