DAX for weighted average instead of simple average

[RGI]

I have a table to provide "Average Value of Transaction" (AVT)

Although this is easy to calculate using SQL statements I want calculated using DAX because I don't want to use simple averages in my reports. Rather, I want weighted averages throughout no matter what the aggregation is.

If you look at the attached image of an Xls sheet it shows the incorrect calculation on row 21, a simple average. The problem with this is that when the system aggregates it creates and average of averages.

I want the DAX to calculate it at any level of aggregation (week, month, store or by another dimension) according to the slicers/filters.

What is the best DAX to achieve this in a calculated column/measure?

[Anonymous]

You can create a quick measure (right click on the field you want to create the wheighted average > create quick measure), select Wheighted as the calculation.

Then drag the base value field from the right to the base value, the value to wheight that data on the second field and the category on the third (this could be the date or the field you will separate your info on the table / graph

Not sure if this is what you needed.

[parry2k]

@RGI try this measure

Avg = DIVIDE( SUM(Table[Sales]), SUM( Table[Transactions] )

[RGI]

@parry2k this gives me an identical calculation for all rows.