cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chuckm4614 Frequent Visitor
Frequent Visitor

Calculating Average

Good Morning, 

 

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

 

I have the following:

ActionTypeDateMonthMonth_NumberDayYearDayNameEnteredByUser
CallAttempt9/4/2018Sep942018TueUser_1
CallAttempt9/4/2018Sep942018TueUser_1
Email9/5/2018Sep952018WedUser_2
CallAttempt9/6/2018Sep962018ThuUser_1
CallAttempt9/6/2018Sep962018ThuUser_1
CallAttempt9/6/2018Sep962018ThuUser_1
FollowUp9/7/2018Sep972018FriUser_2
FollowUp9/7/2018Sep972018FriUser_2
CallAttempt9/10/2018Sep9102018MonUser_1
Search-New9/11/2018Sep9112018TueUser_2
FollowUp9/12/2018Sep9122018WedUser_1
CA_Call9/13/2018Sep9132018ThuUser_2
CA_Call9/13/2018Sep9132018ThuUser_2
CallAttempt9/14/2018Sep9142018FriUser_1
CallAttempt9/17/2018Sep9172018MonUser_1
ScheduledNew9/17/2018Sep9172018MonUser_2
CallAttempt9/18/2018Sep9182018TueUser_1
TA_Existing9/18/2018Sep9182018TueUser_2
TA_Existing9/18/2018Sep9182018TueUser_2
TS_Existing9/18/2018Sep9182018TueUser_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!

 

6 REPLIES 6
DaFloDo Regular Visitor
Regular Visitor

Re: Calculating Average

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

 

avg action types.png

- 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 Frequent Visitor
Frequent Visitor

Re: Calculating Average

Thanks for the reply!

 

I've tried this numerous times and still cant make it work... It returns

 

Clip.png

chuckm4614 Frequent Visitor
Frequent Visitor

Re: Calculating Average

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...

Clip.png

 

 

 

DaFloDo Regular Visitor
Regular Visitor

Re: Calculating Average

hi @chuckm4614,

 

if you can provide your file or an extract of your file, I'll have a look.

 

best regards

 

florian

chuckm4614 Frequent Visitor
Frequent Visitor

Re: Calculating Average

I really appreciate your help!

 

https://www.dropbox.com/s/7i2icnt2ag2ays0/AvgBI.pbix?dl=0

 

Chuck

DaFloDo Regular Visitor
Regular Visitor

Re: Calculating Average

hi Chuck,

 

https://www.dropbox.com/s/qnj4n2eb2ckh2gt/AvgBI.pbix?dl=0

 

can you please try this updated file?

 

I added the relation between the two tables on the Date column and than added the avg-measure.

 

best regards

 

florian