Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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!
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
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...
hi @chuckm4614,
if you can provide your file or an extract of your file, I'll have a look.
best regards
florian
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
Thanks for the reply!
I've tried this numerous times and still cant make it work... It returns
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |