Forum Discussion

AutoJL's avatar
AutoJL
Icon for Helper II rankHelper II
4 years ago
Solved

Filtering out only numeric values for an AVERAGEX

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:   ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

AutoJL's avatar
AutoJL
Icon for Helper II rankHelper II
4 years ago

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!