cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Weighted Averages

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

5 REPLIES 5
Highlighted
Helper I
Helper I

Re: Weighted Averages

Hello!!

 

Hope this helps

 

Measure = AVERAGEA(Hoja2[weight])

Doc:

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

 

Regards!! 

Highlighted
Frequent Visitor

Re: Weighted Averages

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

Highlighted
Helper I
Helper I

Re: Weighted Averages

Hello,

 

I hope this might help you to solve your problem. 

 

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

 

 

Highlighted
Frequent Visitor

Re: Weighted Averages

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.

Highlighted
Super User IV
Super User IV

Re: Weighted Averages

 

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



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors