Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.