Filtering out only numeric values for an AVERAGEX

[AutoJL]

Hi everyone,

I need to filter out only numeric values of a column in order to do a proper AVERAGEX but I am failing horribly to do so and i dont know why.

This is what my data looks like:

Where "number of inputs" is a calculated column. The column "SampleID" is purposely left with Text type to allow me to capture the "No_BARCODE". 

Issue comes when trying to calculate the average of the column "number of inputs" since it is using all the posible values including the "NO_BARCODE" one which is of course wrong ( it is falsely elevating the average ).

I have tried a measure using the AVERAGEX function and then FILTER also but for some reason it is not working, it gives me a blank result.

Average number of inputs = AVERAGEX (FILTER(Samples,ISNUMBER(Samples[SampleID])=TRUE),Samples[Number of inputs])

Can I get some help on why this is not working and how to achieve it correctly?

Many thanks in advance,

Jose

[AutoJL]

Hi amitchandak

Many thanks for the answer! Indeed it is the only non numeric value and of course it worked, I knew there would be a simple solution just couldnt figure it out, I guess I need more practice!

I just marked your post as solution but after confirming that it indeed works I realized there is another issue. Since the table actually contains all the duplicates by running the formula on the whole table all values are being used instead of just once per SampleID instance. 

Any ideas on how to deal with that?

Many thanks again!

[amitchandak · Accepted solution]

@AutoJL , If only NO_BARCODE is text then try like

Average number of inputs = AVERAGEX (FILTER(Samples,Samples[SampleID] <> "NO_BARCODE"),Samples[Number of inputs])

Otherwise explore in power query

Split Column By Digit to Non Digit & Non Digit to Digit: https://youtu.be/tY4Yk1crS9s