Hello - firstly let me begin with I am a novice DAX / PowerBI user. I have searched and found many similar questions, however don't have the skillset to modify my query for my needs. Any assistance would be greatly appreciated!
HERE IS THE MEASURE I CREATED:
Avg Calls/Day Week =
VAR CalcTable =
SUMMARIZE(
'Call Activity',
'Call Activity'[Employee_ID],
'Call Activity'[Call_Dt (bins)],
"#Calls",DISTINCTCOUNT('Call Activity'[Call_ID]))
RETURN
AVERAGEX(CalcTable,[#Calls])
ISSUES:
- the average calculation does not include where employee has ZERO calls for a particular week.
- there are some weeks where employee has no calls and they are not being included in the average calculation.
- the rollup at the MANAGER level and TOTALS should be average for # of weeks displayed (in this case 5 however this is dynamic and views may change to show more weeks)
- Note: I do have another table which houses ALL employee data. I could pull employee ID from the "employee" table if that helps.
EXAMPLE OF DATA RESULTS THAT I AM GETTING
| NAME | 8/21 | 8/28 | 9/4 | 9/11 | 9/18 | TOTAL |
| MANAGER | 13 | 9 | 9 | 11 | | 9 |
| KEN | 10 | | 15 | 12 | | 12 |
| MEL | 15 | 9 | 2 | 10 | | 9 |
EXAMPLE OF DATA RESULTS I WANT TO SEE:
| NAME | 8/21 | 8/28 | 9/4 | 9/11 | 9/18 | TOTAL |
| MANAGER | 13 | 5 | 9 | 11 | 0 | 8 |
| KEN | 10 | 0 | 15 | 12 | 0 | 7.4 |
| MEL | 15 | 9 | 2 | 10 | 0 | 7.2 |
Thank you in advance!
