Forum Discussion
Weighted Average by Category
Hello,
I need assistance calculating 4 different weighted averages by category for Spread to Libor - weighted average by Quarter Year, weighted average by Tenor Year, weighted average by Quarter, and weighted average by Year. Is the best way to calucate in the Power Query Editor or in a measure in the visualization itself? In excel, I would just normal SUMPRODUCT(Amount*Spread to Libor)/SUM(Amount) for each of the different category groups i.e. 1995 or 4|1995 but I can't seem to figure out the conditional weighted average in DAX. I have included a sample of the data below.
Thanks for your help!
| Issue Year | Issue Quarter | Tenor | Spread to Libor | Amount | Quarter Year | Tenor Year |
| 1995 | Q1 | 4 | 20 | 850000000 | 1Q1995 | 4|1995 |
| 1995 | Q1 | 2 | 10 | 50000000 | 1Q1995 | 2|1995 |
| 1995 | Q2 | 2 | 7.5 | 50000000 | 2Q1995 | 2|1995 |
| 1995 | Q1 | 1 | -3 | 50000000 | 1Q1995 | 1|1995 |
| 1996 | Q3 | 2 | 0 | 50000000 | 3Q1996 | 2|1996 |
| 1996 | Q3 | 1 | -2 | 50000000 | 3Q1996 | 1|1996 |
| 1996 | Q3 | 1 | 0 | 50000000 | 3Q1996 | 1|1996 |
| 1996 | Q1 | 1 | 0 | 50000000 | 1Q1996 | 1|1996 |
| 1996 | Q1 | 1 | -283 | 50000000 | 1Q1996 | 1|1996 |
| 1996 | Q1 | 1 | -5 | 57500000 | 1Q1996 | 1|1996 |
| 1997 | Q1 | 2 | -2 | 60000000 | 1Q1997 | 2|1997 |
| 1997 | Q1 | 1 | -5 | 67700000 | 1Q1997 | 1|1997 |
| 1997 | Q1 | 2 | 0 | 70000000 | 1Q1997 | 2|1997 |
| 1997 | Q2 | 1 | -5 | 50000000 | 2Q1997 | 1|1997 |
| 1997 | Q2 | 1 | -5 | 50000000 | 2Q1997 | 1|1997 |
| 1997 | Q2 | 1 | -5 | 50000000 | 2Q1997 | 1|1997 |
| 1997 | Q2 | 2 | 8.6 | 60000000 | 2Q1997 | 2|1997 |
| 1997 | Q2 | 2 | 1 | 50000000 | 2Q1997 | 2|1997 |
| 1997 | Q2 | 3 | 5.5 | 50000000 | 2Q1997 | 3|1997 |
| 1997 | Q2 | 1 | -3 | 50000000 | 2Q1997 | 1|1997 |
| 1997 | Q3 | 4 | 7.55 | 50000000 | 3Q1997 | 4|1997 |
| 1997 | Q3 | 3 | 2.2 | 50000000 | 3Q1997 | 3|1997 |
| 1997 | Q3 | 3 | 6.2 | 50000000 | 3Q1997 | 3|1997 |
| 1997 | Q3 | 3 | 5 | 50000000 | 3Q1997 | 3|1997 |
Hi Anonymous,
You can try to use the following measure formulas:
AVG by QY = CALCULATE ( AVERAGE ( Table[Spread to Libor] ), ALLSELECTED ( Table ), VALUES ( Table[Quarter Year] ) ) AVG by TY = CALCULATE ( AVERAGE ( Table[Spread to Libor] ), ALLSELECTED ( Table ), VALUES ( Table[Tenor Year] ) ) AVG by IQ = CALCULATE ( AVERAGE ( Table[Spread to Libor] ), ALLSELECTED ( Table ), VALUES ( Table[Issue Quarter] ) ) AVG by IY = CALCULATE ( AVERAGE ( Table[Spread to Libor] ), ALLSELECTED ( Table ), VALUES ( Table[Issue Year] ) )Regards,
Xiaoxin Sheng
2 Replies
- amitchandak
Super User
Try like
divide(SUMX(table,table[Amount]*table[Spread to Libor]),SUM(table[Amount]) )
Hi Anonymous,
You can try to use the following measure formulas:
AVG by QY = CALCULATE ( AVERAGE ( Table[Spread to Libor] ), ALLSELECTED ( Table ), VALUES ( Table[Quarter Year] ) ) AVG by TY = CALCULATE ( AVERAGE ( Table[Spread to Libor] ), ALLSELECTED ( Table ), VALUES ( Table[Tenor Year] ) ) AVG by IQ = CALCULATE ( AVERAGE ( Table[Spread to Libor] ), ALLSELECTED ( Table ), VALUES ( Table[Issue Quarter] ) ) AVG by IY = CALCULATE ( AVERAGE ( Table[Spread to Libor] ), ALLSELECTED ( Table ), VALUES ( Table[Issue Year] ) )Regards,
Xiaoxin Sheng
