Power pivot can't calculate average because of text in column

Hey everybody,

I am using power pivot to create an analysis sheet of some sort. The data I am using are the weights of injection moulded products in our factory. I used a combined slicer to be able to sort both product weight and shot weight on product ID. The slicer seems to work fine.
The problem I have is that the power pivot can not calculate the average of the shotweights under products. The column for shotweights contains numbers and text. The reason for having text in the column is because it means the actual measurement is not complete and can not be used in calculating shot weights. A normal pivot table does not have this problem and just does not include the text rows for calculating the average. Any help on how to get a correct average calculation in the power pivot would be appreciated.

The excel sheet is added to this thread.

I'd add a calculated column in your data model that returns the number or 0/BLANK depending on how you want the text treated - e.g.:

=IFERROR([ShotWeight [g]]*100]+0,blank())

I showed you how in my last post.

PP has a very useful BLANK() function (which I really wish they'd add to Excel) which is ignored in calculations.