topic Re: Weighted Average Help in DAX Commands and Tips

Weighted Average Help

laythingy59 — Mon, 15 Aug 2022 12:01:31 GMT

Guys I'm really struggling with DAX and weighted Averages.
I sort of know what I'm doing in SQL, but I can't translate this into DAX. The new quick measure doesn't seem to work either.

I'm trying to get the Average rate(APR) weighted by the value proposed for each month of the year. This is how I've done it for one dataset in SQL.

My other dataset which I'm comparing against is in CSV format and I'm importing it straight into PowerBI.

I'm a complete Novice with DAX and powerbi so please go easy on me 😄

Many thanks

Adam

Re: Weighted Average Help

laythingy59 — Mon, 15 Aug 2022 12:18:25 GMT

Here's some sample data.

Value Proposed,Rate,GO LIVE
1395,19.9,01/01/2022
1795,14.9,01/01/2022
2570,19.9,01/01/2022
1797,19.9,01/01/2022
1418,19.9,01/01/2022
2465,19.9,01/01/2022
1749,19.9,01/01/2022
2650,19.9,01/01/2022
1975,19.9,01/01/2022
2799,14.9,01/01/2022
2530.81,14.9,01/01/2022
3209,19.9,01/01/2022
1745,19.9,01/01/2022
2089,14.9,01/01/2022
1897.2,19.9,01/01/2022
1860,14.9,01/01/2022
2025,19.9,01/01/2022
2494,14.9,01/01/2022
1599,14.9,01/01/2022
6602,14.9,01/01/2022
1775,19.9,01/02/2022
1805,19.9,01/02/2022
2300,14.9,01/02/2022
2395,14.9,01/02/2022
2850,19.9,01/02/2022
4440,19.9,01/02/2022
1795,19.9,01/02/2022
2274,14.9,01/02/2022
2070,14.9,01/02/2022
2218,19.9,01/02/2022
1949,19.9,01/02/2022
4295,19.9,01/02/2022
20000,21.5,01/02/2022
8980,19.9,01/02/2022
10560,19.9,01/02/2022
15000,19.9,01/02/2022
1683,19.9,01/02/2022
9000,19.9,01/02/2022
4501,19.9,01/02/2022
4999,19.9,01/02/2022
9900,21.5,01/02/2022
2300,19.9,01/02/2022
7990,19.9,01/02/2022
3152,21.5,01/02/2022
2700,19.9,01/02/2022
3988.04,19.9,01/02/2022
2109,19.9,01/02/2022
8000,19.9,01/02/2022
2721,19.9,01/02/2022
5800,19.9,01/02/2022
7017,19.9,01/02/2022
1029,19.9,01/02/2022
4251,19.9,01/02/2022
7926,19.9,01/03/2022
5268,19.9,01/03/2022
3000,19.9,01/03/2022
3881,19.9,01/03/2022
1800,19.9,01/03/2022
2000,19.9,01/03/2022
1350,21.5,01/03/2022
2400,19.9,01/03/2022
9924,21.5,01/03/2022
6163,19.9,01/03/2022
1708,19.9,01/03/2022
2272.73,19.9,01/03/2022
2004,19.9,01/03/2022
1950,19.9,01/03/2022
3000,19.9,01/03/2022
4130,19.9,01/03/2022
6884,19.9,01/03/2022
5050,21.5,01/03/2022
4875,19.9,01/03/2022
7100,21.5,01/03/2022
5670,21.5,01/03/2022
2456,19.9,01/03/2022
6100,19.9,01/03/2022
2561,19.9,01/03/2022
10865,19.9,01/03/2022
1099,19.9,01/03/2022
6410,19.9,01/03/2022
4222,19.9,01/03/2022
8941,21.5,01/03/2022
6959,21.5,01/03/2022
2715,19.9,01/03/2022
1670,19.9,01/03/2022
1945,19.9,01/03/2022
6375,19.9,01/03/2022
1849,19.9,01/04/2022
1770,19.9,01/04/2022
1938,14.9,01/04/2022
1645,19.9,01/04/2022
2320,19.9,01/04/2022
1980,19.9,01/04/2022
2015,19.9,01/04/2022
1680,14.9,01/04/2022
1703,19.9,01/04/2022

Re: Weighted Average Help

Jihwan_Kim — Mon, 15 Aug 2022 12:32:10 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

I suggest having a calendar table like below.

Weight Avg measure: = DIVIDE ( SUMX ( Data, Data[Value proposed] * Data[Rate] ), SUMX ( Data, Data[Value proposed] ) )

Re: Weighted Average Help

laythingy59 — Mon, 15 Aug 2022 14:01:00 GMT

Thank you, that's exactly what I was after. I now have to figure out how to put the two sets of results onto one line graph.