Forum Discussion
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:
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.
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
2 Replies
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
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!
