Forum Discussion
Calcuating average for a numerical + text mixed column
Hi, I have a sales column in which some rows have numbers but some have random strings as well. I want to eliminate the strings and only calcualte average for the numbers. I have written thi...
HI Anonymous
Download this sample PBIX file with data and measure
You can create this measure
Sales_Avg = AVERAGEX(FILTER('Sales', IFERROR(VALUE('Sales'[Sales]) >= 0,0)), VALUE('Sales'[Sales]))Regards
Phil
Tahreem24
Super User
Super UserAnonymous ,
DO the below steps:
Step 1: Create a column like below DAX:
Sepearte Num =
VAR IsNum = IF(TableName[Mixed Column] in {"0","1","2","3","4","5","6","7","8","9"},1,0)
VAR num = IF(IsNum=1,(TableName[Mixed Column]))
RETURN num
Step 2: Make the above column to Whole number from format option
Step 3: Create a measure like below:
AVERAGE(TableName[Sepearte Num])