Hey Guys,
How would I create a measure of a weighted average based on 2 different field values? I know how to do this in excel using SUMPRODUCT, but I'm relatively new to DAX and dont know how to do the same calculation that I would normally use in Excel.
For the below, in excel, I would use the following formula in column F:
=SUMPRODUCT(--($B$2:$B$13=B2),--($C$2:$C$13=C2),$D$2:$D$13,$E$2:$E$13/SUMIFS($D$2:$D$13,$C$2:$C$13,"="&C2,$B$2:$B$13,"="&B2))
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Any help would be much appreciated.
How would I create a measure of a weighted average based on 2 different field values? I know how to do this in excel using SUMPRODUCT, but I'm relatively new to DAX and dont know how to do the same calculation that I would normally use in Excel.
For the below, in excel, I would use the following formula in column F:
=SUMPRODUCT(--($B$2:$B$13=B2),--($C$2:$C$13=C2),$D$2:$D$13,$E$2:$E$13/SUMIFS($D$2:$D$13,$C$2:$C$13,"="&C2,$B$2:$B$13,"="&B2))
Date (A) | Month (B) | Language C | Total Calls (D) | Average talk Time E | Average Talk Time Month (F) |
1/1/2019 | 1 | English | 20 | 200 | 165.0 |
1/2/2019 | 1 | English | 30 | 150 | 165.0 |
1/3/2019 | 1 | English | 50 | 160 | 165.0 |
2/1/2019 | 2 | English | 20 | 180 | 181.4 |
2/2/2019 | 2 | English | 20 | 170 | 181.4 |
2/3/2019 | 2 | English | 30 | 190 | 181.4 |
1/1/2019 | 1 | Spanish | 5 | 200 | 224.0 |
1/2/2019 | 1 | Spanish | 10 | 210 | 224.0 |
1/3/2019 | 1 | Spanish | 10 | 250 | 224.0 |
2/1/2019 | 2 | Spanish | 15 | 260 | 241.7 |
2/2/2019 | 2 | Spanish | 10 | 230 | 241.7 |
2/3/2019 | 2 | Spanish | 5 | 210 | 241.7 |
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Any help would be much appreciated.