Re: Calculating Average

chuckm4614 · ‎09-19-2018

Good Morning,

I'm new to Power BI and need a little help in calculating an average.

I have the following:

ActionType	Date	Month	Month_Number	Day	Year	DayName	EnteredByUser
CallAttempt	9/4/2018	Sep	9	4	2018	Tue	User_1
CallAttempt	9/4/2018	Sep	9	4	2018	Tue	User_1
Email	9/5/2018	Sep	9	5	2018	Wed	User_2
CallAttempt	9/6/2018	Sep	9	6	2018	Thu	User_1
CallAttempt	9/6/2018	Sep	9	6	2018	Thu	User_1
CallAttempt	9/6/2018	Sep	9	6	2018	Thu	User_1
FollowUp	9/7/2018	Sep	9	7	2018	Fri	User_2
FollowUp	9/7/2018	Sep	9	7	2018	Fri	User_2
CallAttempt	9/10/2018	Sep	9	10	2018	Mon	User_1
Search-New	9/11/2018	Sep	9	11	2018	Tue	User_2
FollowUp	9/12/2018	Sep	9	12	2018	Wed	User_1
CA_Call	9/13/2018	Sep	9	13	2018	Thu	User_2
CA_Call	9/13/2018	Sep	9	13	2018	Thu	User_2
CallAttempt	9/14/2018	Sep	9	14	2018	Fri	User_1
CallAttempt	9/17/2018	Sep	9	17	2018	Mon	User_1
ScheduledNew	9/17/2018	Sep	9	17	2018	Mon	User_2
CallAttempt	9/18/2018	Sep	9	18	2018	Tue	User_1
TA_Existing	9/18/2018	Sep	9	18	2018	Tue	User_2
TA_Existing	9/18/2018	Sep	9	18	2018	Tue	User_2
TS_Existing	9/18/2018	Sep	9	18	2018	Tue	User_2

and need to calculate the AVERAGE number of ActionType per day. I need to exclude the weekends or only count the days with an action.

Thanks in advance for the help!

DaFloDo · ‎09-19-2018

hi @chuckm4614,

please see if the solution in the linked pbix fits your needs.

https://www.dropbox.com/s/qol9gyvluavx13b/avg%20number%20of%20ActionType%20per%20day.pbix?dl=0

- created a calender table called days (maybe you have this already, you could also create measures that work directly with your data table)

Days = CALENDAR(min('table'[Date]);max('table'[Date]))

- created a new flag column that states is the date is weekend or not

Weekend = if(or( WEEKDAY(Days[Date])=1; WEEKDAY(Days[Date])=7);True();False())

- created the average measure

avg = CALCULATE(AVERAGEX(Days;[cnt]);ALLEXCEPT(Days;Days[date]);Days[Weekend]=FALSE())

best regards

florian

chuckm4614 · ‎09-20-2018

Thank You for the reply..

I've attempted your solution numerous times and cannot make it calculate correctly. I've compared your pbix file to mine line by line... not sure what's up...