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))
[TABLE="width: 1049"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date (A)[/TD]
[TD]Month (B)[/TD]
[TD]Language C[/TD]
[TD]Total Calls (D)[/TD]
[TD]Average talk Time E[/TD]
[TD]Average Talk Time Month (F)[/TD]
[/TR]
[TR]
[TD]1/1/2019[/TD]
[TD]1[/TD]
[TD]English[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]165.0[/TD]
[/TR]
[TR]
[TD]1/2/2019[/TD]
[TD]1[/TD]
[TD]English[/TD]
[TD]30[/TD]
[TD]150[/TD]
[TD]165.0[/TD]
[/TR]
[TR]
[TD]1/3/2019[/TD]
[TD]1[/TD]
[TD]English[/TD]
[TD]50[/TD]
[TD]160[/TD]
[TD]165.0[/TD]
[/TR]
[TR]
[TD]2/1/2019[/TD]
[TD]2[/TD]
[TD]English[/TD]
[TD]20[/TD]
[TD]180[/TD]
[TD]181.4[/TD]
[/TR]
[TR]
[TD]2/2/2019[/TD]
[TD]2[/TD]
[TD]English[/TD]
[TD]20[/TD]
[TD]170[/TD]
[TD]181.4[/TD]
[/TR]
[TR]
[TD]2/3/2019[/TD]
[TD]2[/TD]
[TD]English[/TD]
[TD]30[/TD]
[TD]190[/TD]
[TD]181.4[/TD]
[/TR]
[TR]
[TD]1/1/2019[/TD]
[TD]1[/TD]
[TD]Spanish[/TD]
[TD]5[/TD]
[TD]200[/TD]
[TD]224.0[/TD]
[/TR]
[TR]
[TD]1/2/2019[/TD]
[TD]1[/TD]
[TD]Spanish[/TD]
[TD]10[/TD]
[TD]210[/TD]
[TD]224.0[/TD]
[/TR]
[TR]
[TD]1/3/2019[/TD]
[TD]1[/TD]
[TD]Spanish[/TD]
[TD]10[/TD]
[TD]250[/TD]
[TD]224.0[/TD]
[/TR]
[TR]
[TD]2/1/2019[/TD]
[TD]2[/TD]
[TD]Spanish[/TD]
[TD]15[/TD]
[TD]260[/TD]
[TD]241.7[/TD]
[/TR]
[TR]
[TD]2/2/2019[/TD]
[TD]2[/TD]
[TD]Spanish[/TD]
[TD]10[/TD]
[TD]230[/TD]
[TD]241.7[/TD]
[/TR]
[TR]
[TD]2/3/2019[/TD]
[TD]2[/TD]
[TD]Spanish[/TD]
[TD]5[/TD]
[TD]210[/TD]
[TD]241.7[/TD]
[/TR]
</tbody>[/TABLE]
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))
[TABLE="width: 1049"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date (A)[/TD]
[TD]Month (B)[/TD]
[TD]Language C[/TD]
[TD]Total Calls (D)[/TD]
[TD]Average talk Time E[/TD]
[TD]Average Talk Time Month (F)[/TD]
[/TR]
[TR]
[TD]1/1/2019[/TD]
[TD]1[/TD]
[TD]English[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]165.0[/TD]
[/TR]
[TR]
[TD]1/2/2019[/TD]
[TD]1[/TD]
[TD]English[/TD]
[TD]30[/TD]
[TD]150[/TD]
[TD]165.0[/TD]
[/TR]
[TR]
[TD]1/3/2019[/TD]
[TD]1[/TD]
[TD]English[/TD]
[TD]50[/TD]
[TD]160[/TD]
[TD]165.0[/TD]
[/TR]
[TR]
[TD]2/1/2019[/TD]
[TD]2[/TD]
[TD]English[/TD]
[TD]20[/TD]
[TD]180[/TD]
[TD]181.4[/TD]
[/TR]
[TR]
[TD]2/2/2019[/TD]
[TD]2[/TD]
[TD]English[/TD]
[TD]20[/TD]
[TD]170[/TD]
[TD]181.4[/TD]
[/TR]
[TR]
[TD]2/3/2019[/TD]
[TD]2[/TD]
[TD]English[/TD]
[TD]30[/TD]
[TD]190[/TD]
[TD]181.4[/TD]
[/TR]
[TR]
[TD]1/1/2019[/TD]
[TD]1[/TD]
[TD]Spanish[/TD]
[TD]5[/TD]
[TD]200[/TD]
[TD]224.0[/TD]
[/TR]
[TR]
[TD]1/2/2019[/TD]
[TD]1[/TD]
[TD]Spanish[/TD]
[TD]10[/TD]
[TD]210[/TD]
[TD]224.0[/TD]
[/TR]
[TR]
[TD]1/3/2019[/TD]
[TD]1[/TD]
[TD]Spanish[/TD]
[TD]10[/TD]
[TD]250[/TD]
[TD]224.0[/TD]
[/TR]
[TR]
[TD]2/1/2019[/TD]
[TD]2[/TD]
[TD]Spanish[/TD]
[TD]15[/TD]
[TD]260[/TD]
[TD]241.7[/TD]
[/TR]
[TR]
[TD]2/2/2019[/TD]
[TD]2[/TD]
[TD]Spanish[/TD]
[TD]10[/TD]
[TD]230[/TD]
[TD]241.7[/TD]
[/TR]
[TR]
[TD]2/3/2019[/TD]
[TD]2[/TD]
[TD]Spanish[/TD]
[TD]5[/TD]
[TD]210[/TD]
[TD]241.7[/TD]
[/TR]
</tbody>[/TABLE]
Any help would be much appreciated.