Calculate Average of "Not Empty" Cell

[Anonymous]

Hello,

I have a data source, which is an excel table that looks something like this:

I want to import it into PBI Desktop and calculate the average of non-blank items (the result in this case would be 50). Any suggestion how I could do it effectively?

Thank you,

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

1. Calculated column

Average = 
IF ( [Value] == BLANK (), BLANK (), AVERAGE ( 'Table'[Value] ) )

2. Measure

Average M = 
IF ( MAX('Table'[Value]) == BLANK (), BLANK (), AVERAGE ( 'Table'[Value] ) )

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nathaniel_C]

hI @Anonymous , use COUNTA()

Count for average = 


var _sum = SUM(FruitTable[Count])
var _count = Counta(FruitTable[Count])

return DIVIDE(_sum,_count)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel