Most efficient way to calculate AverageX for several columns from different tables

[michaelsh]

Hello friends,

My data looks as follows - it is Sales table, each row - is one sale transaction.

I need to calculate average sales amount per Salesperson per Month (without showing any column on axis, just measure)

I know how to do it if it were to calculate avg per one column, say Month:

AVERAGEX(VALUES(Calendar[Month]),SUM(Sales[Amt]))

How do I calculate the average per two (or more) columns that are also coming from different tables (Calendar, Sales...)?

I am looking for the most efficient way (from query speed performance perspective)

Thank you

[michaelsh]

@amitchandak  
Actually, here I see that SUMMARIZE is not the most efficient way to do agregation calculations...

[amitchandak]

@michaelsh , I tried to summarize columns in past. somehow it did not work in this scenario. 

If this is one group by, I prefer values. 

I will continue to play around on this. Will update, 

[v-eqin-msft]

Hi @michaelsh ,

Could you tell me if your problem has been solved? If it is, kindly Accept @amitchandak 's reply as the solution. More people will benefit from it.

Or please provide me with more details about your two tables (Calendar and Sales) and the relationships or share me with your pbix file from your Onedrive for Business after removing sensitive data.

Best Regards,
Eyelyn Qin

[amitchandak · Accepted solution]

@michaelsh , Try a measure like

AVERAGEX(Summarize(Sales, Sales[Salesperson],Calendar[Month],"_1",SUM(Sales[Amt])),[_1])

[michaelsh]

Excellent, @amitchandak it works!

Interesting, although we summarize Sales table, we can use Month column from another, Calendar table.

Thanks!