topic DAX for weighted average instead of simple average in DAX Commands and Tips

DAX for weighted average instead of simple average

RGI — Mon, 03 Jun 2019 16:19:18 GMT

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?

Re: DAX for weighted average instead of simple average

parry2k — Mon, 03 Jun 2019 16:54:33 GMT

RGI try this measure

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

Re: DAX for weighted average instead of simple average

RGI — Mon, 03 Jun 2019 20:52:24 GMT

parry2k this gives me an identical calculation for all rows.

Re: DAX for weighted average instead of simple average

Anonymous — Tue, 04 Jun 2019 15:03:27 GMT

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.