Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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!
Solved! Go to 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])
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.
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)
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).
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?
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])
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |