Forum Discussion
Average For String Data Type
Hi ALL,
I have a column Amount but it is in varchar datatype but here my problem is I have to calculate the average of the Amount
table A (Id, Amount)
1 10.00
2 20.00
3 50.00
1 15.00
2 25.00
3 55.00
Average of all customer = Average(Table A[Amount]) but here the Amount is in varchar data type and I am using SQL Server as a direct query result =(10+20+50+15+25+55)/3=58.33
In a direct query, you have to use another option cast to a number. By *1 or by bring number from DB
Hi, manideep547
Based on your description, you can use Value function to convert from text to number when it is calculated. Therefore, you may create a measure as follows.
AverageMeasure = DIVIDE( SUMX( 'Table A', VALUE('Table A'[Amount]) ), DISTINCTCOUNT('Table A'[Id]) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Why you can not change the data type in power BI.
Try Averagex(table, Amount*1.0), If it error, then filter the table for those items
like
Averagex(filter(table,not(isblank(amount)), Amount*1.0),
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedinamitchandak For direct query can we change the data type?
can you please suggest me if how can change the data type for a column in direct query mode?
thank you.In a direct query, you have to use another option cast to a number. By *1 or by bring number from DB
Hi, manideep547
Based on your description, you can use Value function to convert from text to number when it is calculated. Therefore, you may create a measure as follows.
AverageMeasure = DIVIDE( SUMX( 'Table A', VALUE('Table A'[Amount]) ), DISTINCTCOUNT('Table A'[Id]) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, manideep547
If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
Best Regards
Allan