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

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Interest Rate -Weighted Average

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
Super User IV
Super User IV

Re: Interest Rate -Weighted Average

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 Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





KelvinMorel Helper II
Helper II

Re: Interest Rate -Weighted Average

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?

cchavez
Frequent Visitor

Re: Interest Rate -Weighted Average

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"

 

KelvinMorel Helper II
Helper II

Re: Interest Rate -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,

MarcelBeug Community Champion
Community Champion

Re: Interest Rate -Weighted Average

@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)
KelvinMorel Helper II
Helper II

Re: Interest Rate -Weighted Average

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.

 

 

Highlighted
Super User III
Super User III

Re: Interest Rate -Weighted Average

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

Super User IV
Super User IV

Re: Interest Rate -Weighted Average

Hi,

 

Try this

 

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


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

Re: Interest Rate -Weighted Average

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

Top Solution Authors
Top Kudoed Authors