topic Re: Weighted Averages in DAX Commands and Tips

Weighted Averages

Anonymous — Thu, 27 Feb 2020 20:46:09 GMT

Hello everyone,

I think I know the answer, which would be a pain, so I'm hoping there is an easier solution. I am trying to find a weighted average for a column but some of the users don't populate one of the fields on a consistent basis or have that information. Using the normal weighted average calcuations throws the true weighted average off because it is counting the balance which produces an incorrect weighted average calculation. I know I can go in and edit the query, remove all the rows that are blank or 0 in that column, rename the query and then do it that way, however, I was looking to see if there was a DAX function that would ignore the row altogether if that field is blank or 0. I value your thoughts on this. Thanks

Re: Weighted Averages

Anonymous — Fri, 28 Feb 2020 01:34:22 GMT

Hello!!

Hope this helps

Measure = AVERAGEA(Hoja2[weight])

Doc:

https://docs.microsoft.com/en-us/dax/averagea-function-dax

Regards!!

Re: Weighted Averages

Anonymous — Tue, 03 Mar 2020 21:40:05 GMT

Thanks JoseCruzCat. Unfortunately that does the average correctly, but it does not work when trying to calculate a weighted average.

Re: Weighted Averages

piyushszope — Wed, 04 Mar 2020 08:05:46 GMT

Hello,

I hope this might help you to solve your problem.

Weighted =
SUM ( 'Table'[weight] )
/ (COUNTROWS('Table') - COUNTROWS(FILTER('Table','Table'[weight]=0)))

Re: Weighted Averages

Anonymous — Wed, 04 Mar 2020 15:50:47 GMT

Thanks but that doesn't work either. Here is an example:

Customers supply their yearly income and their age.

Not all of the customers supply their age but do supply their yearly income.

I would want to take the weighted average of the age of customers by their incomes. For customers who don't supply their age I want to exclude their income and the entire row in the calcuations. Within Excel it would be (Age*Yearly Income)/Yearly Income after deleting all of the rows of the customers who didn't supply their age. So basically I would want PowerBI to exclude the rows where the age is blank and only do the function on rows with both data sets in the query.

Re: Weighted Averages

Anonymous — Wed, 04 Mar 2020 16:52:27 GMT

// For all visible customers in the Customers // dimension table the formula will calculate // the age-weighted average income using // only those customers that have supplied // their age. // Remember that a BLANK is treated as // either '' (strings) or 0 (numbers), // therefore the below is enough to get // the weighted average. [Age-Weighted Average Income] = var __numerator = SUMX( Customers, Customers[Age] * Customers[Income] ) var __denominator = SUM( Customers[Age] ) var __avg = DIVIDE( __numerator, __denominator ) return __avg

And a mirror measure...

[Income-Weighted Average Age] = var __numerator = SUMX( Customers, Customers[Age] * Customers[Income] ) var __denominator = SUM( Customers[Income] ) var __avg = DIVIDE( __numerator, __denominator ) return __avg

Hope this helps.

Best