How to calculate average in a column with string value on it

[Anonymous]

Hey guys, 

I'm quite new on PowerBi usage and I need your help.

In the column I have a mix of number of values (represented as "OK"), in my dashboard I need to create a box with a formula on it where I filter out the rows with value "OK" then calculate the average of the remaning.

Suggestions? 

[tackytechtom · Accepted solution]

Hi @Anonymous ,

How about this:

Here the code:

MeasureText&Numbers = 
CALCULATE ( 
    AVERAGEX ( Table20, CONVERT ( Table20[Durata step 2], INTEGER) ),
    Table20[Durata step 2] <> "OK"
)

While the measure above should work, I'd recommend to clean your data in that column before loading it into PBI. If the OKs and the numbers are important, I'd suggest to create a new column with the OKs and substitute the OKs in Durata Step 2 with blanks() in Power Query. But that shall just be a humble suggestion 🙂

Hope the formular gets you closer to a solution anyway 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/