Average For String Data Type

[manideep547]

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

[amitchandak · Accepted solution]

In a direct query, you have to use another option cast to a number. By *1 or by bring number from DB

[v-alq-msft]

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

[v-alq-msft · Accepted solution]

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.

[amitchandak]

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 Linkedin

[manideep547]

@amitchandak  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.

[amitchandak · Accepted solution]

In a direct query, you have to use another option cast to a number. By *1 or by bring number from DB

[manideep547]

@amitchandak Is there is an option to change the mode from import to direct query?

[v-alq-msft]

Hi, @manideep547 

It is unsupported to change from import to DirectQuery mode. While it is possible to switch a model from DirectQuery mode to use import mode, all the necessary data must be imported. It's also not possible to switch back, primarily because of the set of features not supported in DirectQuery mode.

For further information, you may refer to the following link.

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

Best Regards

Allan