Forum Discussion
Include Blank Values in Summarize and AverageX Calculation
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])
- 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.
| 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 |
| 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 |
3 Replies
v-jianboli-msftCommunity Support
Hi sg919 ,
Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?
Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hopefully you can access this link: Example.pbi file
v-jianboli-msft Quick Note: original question was for the Call Activity table. Example I provided is for Opportunity table. Trying to achieve the same objectives though.
