cancel
Showing results for
Did you mean:
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

 ACM RATE PERIODE WEIGHTED Salesman1 2,986 72 Salesman1 2,785 48 Salesman2 2,682 60 Salesman4 2,580 36 Salesman2 1,723 60 Salesman2 4,230 72 Salesman2 1,917 72 Salesman1 3,665 24 Salesman1 2,668 72 Salesman3 1,764 84 Salesman2 4,663 60 Salesman2 1,663 60

How can get it done in DAX?

Thx

1 ACCEPTED SOLUTION
Super User II

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!

9 REPLIES 9
Super User III

Hi,

Try this

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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

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"

Helper II

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,

Community Champion

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

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.

Super User II

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!

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 B C F G ACM RATE PERIODE Salesmen WRate Salesman1 2,986 72 Salesman1 2,911 Salesman1 3 48 Salesman2 2,829 Salesman2 3 60 Salesman3 1,764 Salesman4 2,580 36 Salesman4 2,580 Salesman2 1,723 60 Salesman2 4,230 72 Salesman2 1,917 72 Salesman1 3,665 24 Salesman1 2,668 72 Salesman3 1,764 84 Salesman2 4,663 60 Salesman2 1,663 60

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

Super User III

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

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

Announcements