Weighted Averages

[Anonymous]

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

[Anonymous]

// 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

D

[piyushszope]

Hello,

I hope this might help you to solve your problem. 

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

[Anonymous]

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.

[Anonymous]

Hello!!

Hope this helps

Measure = AVERAGEA(Hoja2[weight])

Doc:

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

Regards!! 

[Anonymous]

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