topic Re: Interest Rate -Weighted Average in Desktop
https://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301395#M133175
<P>Found how to do it in MS Excel, highlighted Salesman3 and Salesman4 as check data.</P><P>In cell <STRONG>G1</STRONG> <EM>=SUMPRODUCT($B$2:$B$13;$C$2:$C$13;--($A$2:$A$13=F1))/SUMIF($A$2:$C$13;F1;$C$2:$C$13)</EM></P><TABLE><TBODY><TR><TD> A</TD><TD>B </TD><TD>C </TD><TD> </TD><TD> </TD><TD>F </TD><TD>G </TD></TR><TR><TD>ACM</TD><TD>RATE</TD><TD>PERIODE</TD><TD> </TD><TD> </TD><TD>Salesmen</TD><TD>WRate</TD></TR><TR><TD>Salesman1</TD><TD>2,986</TD><TD>72</TD><TD> </TD><TD> </TD><TD>Salesman1</TD><TD>2,911</TD></TR><TR><TD>Salesman1</TD><TD>3</TD><TD>48</TD><TD> </TD><TD> </TD><TD>Salesman2</TD><TD>2,829</TD></TR><TR><TD>Salesman2</TD><TD>3</TD><TD>60</TD><TD> </TD><TD> </TD><TD><STRONG>Salesman3</STRONG></TD><TD><STRONG>1,764</STRONG></TD></TR><TR><TD>S<STRONG>alesman4</STRONG></TD><TD><STRONG>2,580</STRONG></TD><TD><STRONG>36</STRONG></TD><TD> </TD><TD> </TD><TD><STRONG>Salesman4</STRONG></TD><TD><STRONG>2,580</STRONG></TD></TR><TR><TD>Salesman2</TD><TD>1,723</TD><TD>60</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Salesman2</TD><TD>4,230</TD><TD>72</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Salesman2</TD><TD>1,917</TD><TD>72</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Salesman1</TD><TD>3,665</TD><TD>24</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Salesman1</TD><TD>2,668</TD><TD>72</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD><STRONG>Salesman3</STRONG></TD><TD><STRONG>1,764</STRONG></TD><TD><STRONG>84</STRONG></TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Salesman2</TD><TD>4,663</TD><TD>60</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Salesman2</TD><TD>1,663</TD><TD>60</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>So I can achive this but will need to created another table/colomn, this should to be possible in DAX, right?</P>Sun, 12 Nov 2017 17:50:47 GMTKelvinMorel2017-11-12T17:50:47ZInterest Rate -Weighted Average
https://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301339#M133145
<P>Hi,</P><P> </P><P>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</P><P> </P><TABLE><TBODY><TR><TD>ACM</TD><TD>RATE</TD><TD>PERIODE</TD><TD>WEIGHTED</TD></TR><TR><TD>Salesman1</TD><TD>2,986</TD><TD>72</TD><TD> </TD></TR><TR><TD>Salesman1</TD><TD>2,785</TD><TD>48</TD><TD> </TD></TR><TR><TD>Salesman2</TD><TD>2,682</TD><TD>60</TD><TD> </TD></TR><TR><TD>Salesman4</TD><TD>2,580</TD><TD>36</TD><TD> </TD></TR><TR><TD>Salesman2</TD><TD>1,723</TD><TD>60</TD><TD> </TD></TR><TR><TD>Salesman2</TD><TD>4,230</TD><TD>72</TD><TD> </TD></TR><TR><TD>Salesman2</TD><TD>1,917</TD><TD>72</TD><TD> </TD></TR><TR><TD>Salesman1</TD><TD>3,665</TD><TD>24</TD><TD> </TD></TR><TR><TD>Salesman1</TD><TD>2,668</TD><TD>72</TD><TD> </TD></TR><TR><TD>Salesman3</TD><TD>1,764</TD><TD>84</TD><TD> </TD></TR><TR><TD>Salesman2</TD><TD>4,663</TD><TD>60</TD><TD> </TD></TR><TR><TD>Salesman2</TD><TD>1,663</TD><TD>60</TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>How can get it done in DAX?</P><P> </P><P>Thx</P>Sun, 12 Nov 2017 06:50:05 GMThttps://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301339#M133145KelvinMorel2017-11-12T06:50:05ZRe: Interest Rate -Weighted Average
https://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301392#M133172
<P>drop salesman and rate on your tabel visual, in drop down next to rate column, select average as aggregated method. </P>Sun, 12 Nov 2017 14:46:22 GMThttps://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301392#M133172parry2k2017-11-12T14:46:22ZRe: Interest Rate -Weighted Average
https://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301395#M133175
<P>Found how to do it in MS Excel, highlighted Salesman3 and Salesman4 as check data.</P><P>In cell <STRONG>G1</STRONG> <EM>=SUMPRODUCT($B$2:$B$13;$C$2:$C$13;--($A$2:$A$13=F1))/SUMIF($A$2:$C$13;F1;$C$2:$C$13)</EM></P><TABLE><TBODY><TR><TD> A</TD><TD>B </TD><TD>C </TD><TD> </TD><TD> </TD><TD>F </TD><TD>G </TD></TR><TR><TD>ACM</TD><TD>RATE</TD><TD>PERIODE</TD><TD> </TD><TD> </TD><TD>Salesmen</TD><TD>WRate</TD></TR><TR><TD>Salesman1</TD><TD>2,986</TD><TD>72</TD><TD> </TD><TD> </TD><TD>Salesman1</TD><TD>2,911</TD></TR><TR><TD>Salesman1</TD><TD>3</TD><TD>48</TD><TD> </TD><TD> </TD><TD>Salesman2</TD><TD>2,829</TD></TR><TR><TD>Salesman2</TD><TD>3</TD><TD>60</TD><TD> </TD><TD> </TD><TD><STRONG>Salesman3</STRONG></TD><TD><STRONG>1,764</STRONG></TD></TR><TR><TD>S<STRONG>alesman4</STRONG></TD><TD><STRONG>2,580</STRONG></TD><TD><STRONG>36</STRONG></TD><TD> </TD><TD> </TD><TD><STRONG>Salesman4</STRONG></TD><TD><STRONG>2,580</STRONG></TD></TR><TR><TD>Salesman2</TD><TD>1,723</TD><TD>60</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Salesman2</TD><TD>4,230</TD><TD>72</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Salesman2</TD><TD>1,917</TD><TD>72</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Salesman1</TD><TD>3,665</TD><TD>24</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Salesman1</TD><TD>2,668</TD><TD>72</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD><STRONG>Salesman3</STRONG></TD><TD><STRONG>1,764</STRONG></TD><TD><STRONG>84</STRONG></TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Salesman2</TD><TD>4,663</TD><TD>60</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Salesman2</TD><TD>1,663</TD><TD>60</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>So I can achive this but will need to created another table/colomn, this should to be possible in DAX, right?</P>Sun, 12 Nov 2017 17:50:47 GMThttps://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301395#M133175KelvinMorel2017-11-12T17:50:47ZRe: Interest Rate -Weighted Average
https://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301412#M133180
<P>Hi Kelvin, can you explain better whats you need? What´s the relation between RATE & PERIODE...</P><P> </P><P>Doesn´t clear for me, the measure "Weighted Average"</P><P> </P>Sun, 12 Nov 2017 16:44:38 GMThttps://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301412#M133180cchavez2017-11-12T16:44:38ZRe: Interest Rate -Weighted Average
https://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301425#M133184
<P>Hi CCHAVEZ,</P><P> </P><P>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.</P><P>For instance:</P><P>Case 1:</P><P>Contract: 120.000,00€ - Rate: 3,50% - Periode: 36 months</P><P><SPAN>Contract: </SPAN>100.000,00€ - Rate: 2,00% - Periode: 24 months</P><P> </P><P>Case 2:</P><P><SPAN>Contract: </SPAN>120.000,00€ - Rate: 3,50% - Periode: 6 months</P><P><SPAN>Contract: </SPAN>100.000,00€ - Rate: 2,00% - <SPAN>Periode: 12 months</SPAN></P><P> </P><P><SPAN>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%</SPAN></P><P>Excel formula:</P><P>=SUMPRODUCT([Array1];[Array2])/SUM([Array2])</P><P>or</P><P>=((A1*B1)+(A2*B2))/SUM(B1:B2)</P><P> </P><P>Hoop this is clear for you...</P><P> </P><P>Appreciate help,</P>Sun, 12 Nov 2017 18:08:06 GMThttps://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301425#M133184KelvinMorel2017-11-12T18:08:06ZRe: Interest Rate -Weighted Average
https://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301428#M133186
<P><LI-USER uid="46699"></LI-USER> why don't you just refer to <A href="https://community.powerbi.com/t5/Desktop/Salesmen-yearly-interest-rate-average-target/m-p/301252#M133106" target="_self">your previous post</A>? This explains where the weighted average comes from. </P>Sun, 12 Nov 2017 18:56:05 GMThttps://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301428#M133186MarcelBeug2017-11-12T18:56:05ZRe: Interest Rate -Weighted Average
https://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301429#M133187
<P>Hi MarcelBeug,</P><P> </P><P>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.</P><P> </P><P> </P>Sun, 12 Nov 2017 19:01:24 GMThttps://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301429#M133187KelvinMorel2017-11-12T19:01:24ZRe: Interest Rate -Weighted Average
https://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301439#M133194
<P>The DAX-measure would look like this:</P><PRE>WAvg:=SUMX(Table1,Table1[RATE]*Table1[PERIODE])/SUM([PERIODE])</PRE><P>here is how it works:</P><P><A href="https://powerpivotpro.com/2012/05/weighted-averages-another-use-of-sumx/" target="_blank">https://powerpivotpro.com/2012/05/weighted-averages-another-use-of-sumx/</A></P>Sun, 12 Nov 2017 20:29:32 GMThttps://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301439#M133194ImkeF2017-11-12T20:29:32ZRe: Interest Rate -Weighted Average
https://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301493#M133227
<P>Hi,</P><P> </P><P>Try this</P><P> </P><P>=SUMX(Data,Data[Rate]*Data[Periode])/COUNTROWS(Data)</P>Mon, 13 Nov 2017 00:17:30 GMThttps://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301493#M133227Ashish_Mathur2017-11-13T00:17:30ZRe: Interest Rate -Weighted Average
https://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301612#M133276
<P>Hi Ashish_Mathur,</P><P> </P><P>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?</P><P> </P><P>Many thx</P>Mon, 13 Nov 2017 04:55:37 GMThttps://community.powerbi.com/t5/Desktop/Interest-Rate-Weighted-Average/m-p/301612#M133276KelvinMorel2017-11-13T04:55:37Z