cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JakeSWTC Frequent Visitor
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.

 

1.png

 

2.png

 

Thank you!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
JakeSWTC Frequent Visitor
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
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
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).

 

3.png

Community Support Team
Community Support Team

Re: Percentage of values in column

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]

 1.png

 

 

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
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
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!

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 122 members 1,674 guests
Please welcome our newest community members: