cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KelvinMorel
Helper II
Helper II

Interest Rate -Weighted Average

Hi,

 

I'm new to POWER BI, would like to calculate "Weighted Average" of salesmen but can't get around this, this sample of my data

 

ACMRATEPERIODEWEIGHTED
Salesman12,98672 
Salesman12,78548 
Salesman22,68260 
Salesman42,58036 
Salesman21,72360 
Salesman24,23072 
Salesman21,91772 
Salesman13,66524 
Salesman12,66872 
Salesman31,76484 
Salesman24,66360 
Salesman21,66360 

 

How can get it done in DAX?

 

Thx

1 ACCEPTED SOLUTION

The DAX-measure would look like this:

WAvg:=SUMX(Table1,Table1[RATE]*Table1[PERIODE])/SUM([PERIODE])

here is how it works:

https://powerpivotpro.com/2012/05/weighted-averages-another-use-of-sumx/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User III
Super User III

Hi,

 

Try this

 

=SUMX(Data,Data[Rate]*Data[Periode])/COUNTROWS(Data)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish_Mathur,

 

ImkeF solution is the sintax I was looking for but maybe yours can help out in the future, how do I need to read/understand that?

 

Many thx

cchavez
Frequent Visitor

Hi Kelvin, can you explain better whats you need? What´s the relation between RATE & PERIODE...

 

Doesn´t clear for me, the measure "Weighted Average"

 

Hi CCHAVEZ,

 

As shown in my Excel exercice, I need to calculate the "Interest Rate Weighted Average" by Salesmen, this should be the relation between the interest rate given to customers and the length of the contract.

For instance:

Case 1:

Contract: 120.000,00€ - Rate: 3,50% - Periode: 36 months

Contract: 100.000,00€ - Rate: 2,00% - Periode: 24 months

 

Case 2:

Contract: 120.000,00€ - Rate: 3,50% - Periode: 6 months

Contract: 100.000,00€ - Rate: 2,00% - Periode: 12 months

 

In both cases average rate it's 2,75% but this a straight average and doens't take in consideration the length of contracts, with weighted avg case 1 is: 2,9% and case 2 is: 2,5%

Excel formula:

=SUMPRODUCT([Array1];[Array2])/SUM([Array2])

or

=((A1*B1)+(A2*B2))/SUM(B1:B2)

 

Hoop this is clear for you...

 

Appreciate help,

@KelvinMorel why don't you just refer to your previous post? This explains where the weighted average comes from. 

Specializing in Power Query Formula Language (M)

Hi MarcelBeug,

 

Right, the previews post help me to make it in excel but can't figure out how to do it in Power BI, my apologizes.

 

 

The DAX-measure would look like this:

WAvg:=SUMX(Table1,Table1[RATE]*Table1[PERIODE])/SUM([PERIODE])

here is how it works:

https://powerpivotpro.com/2012/05/weighted-averages-another-use-of-sumx/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

KelvinMorel
Helper II
Helper II

Found how to do it in MS Excel, highlighted Salesman3 and Salesman4 as check data.

In cell G1 =SUMPRODUCT($B$2:$B$13;$C$2:$C$13;--($A$2:$A$13=F1))/SUMIF($A$2:$C$13;F1;$C$2:$C$13)

 A  
ACMRATEPERIODE  SalesmenWRate
Salesman12,98672  Salesman12,911
Salesman1348  Salesman22,829
Salesman2360  Salesman31,764
Salesman42,58036  Salesman42,580
Salesman21,72360    
Salesman24,23072    
Salesman21,91772    
Salesman13,66524    
Salesman12,66872    
Salesman31,76484    
Salesman24,66360    
Salesman21,66360    
       
 
       

 

So I can achive this but will need to created another table/colomn, this should to be possible in DAX, right?

parry2k
Super User III
Super User III

drop salesman and rate on your tabel visual, in drop down next to rate column, select average as aggregated method. 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors