AVG by Month

[jbenson]

i have a column called Basis Month and another column called Has Basis. I have been trying to find a formula online where i can find the average basis per month. Every formula I have tried does not work. Any help would be great! 

Thank you 

[mahoneypat]

Please try this measure expression, replacing Table with your actual table name.

Avg By Month = AVERAGEX(VALUES(Table[Basis Month]), CALCULATE(AVERAGE(Table[Has Basis])))

Pat

[jbenson]

Here is what i typed in:

AVG by Month = AVERAGEX(VALUES('Corn&Bean Purchase'[Basis Month]),CALCULATE(AVERAGE('Corn&Bean Purchase'[Has Basis])))

The error i get is, The Function AVERAGE cannot work with values of tyoe String. I am not sure what I am doing wrong

[mahoneypat · Accepted solution]

Check the type on your Has Basis column.  If it is text, then convert it to decimal in the query editor.

Pat

[justinh]

Group the table (or a copy of the table). Group by [Basis Month] and choose to Average [Has Basis].

Right click on the [Basis Month] column header and select "Group By" to get started.