Forum Discussion
Calculating Average Weekday Users per week
Hello Everyone,
I'm interested in determining the Average number Users per weekday (ignoring Saturday && Sunday). So for example below, I would have Sept 10 - Sept 14 aggregrations (DistinctCount) summed and then divided by 5 weekdays for an average of: 349. I have a date table. Can someone please help me with the DAX? How can I attach my workbook for others to experiment with?
Thanks!
Matthew
Hi Matthew_Theis,
Please check my steps as below.
1. Create a Calendar table and create some calculated column in the new table.
Calendar = CALENDARAUTO()
weekday = WEEKDAY('Calendar'[Date])YearWeek = YEAR('Calendar'[Date])*100+WEEKNUM('Calendar'[Date])users = CALCULATE(DISTINCTCOUNT('WebSpice Log'[IP]))userswithworkdays = IF('Calendar'[weekday]=7 || 'Calendar'[weekday]=1,BLANK(),'Calendar'[users])2. Then we can create a measure to achieve our goal.
average = CALCULATE(SUM('Calendar'[userswithworkdays]),ALLSELECTED('Calendar'),VALUES('Calendar'[YearWeek]))/5For more details, please check the pbix as attached.
Regards,
Frank
3 Replies
If you have confirmed your email address, you can post PBIX files I believe. Otherwise, share it out on OneDrive or Box. Or, just post sample data in text format that can be copied and supply your calculations, etc.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Here is a link to the .pbix. Hopefully this will work. I don't see a Choose File option as mentioned here.
Hi Matthew_Theis,
Please check my steps as below.
1. Create a Calendar table and create some calculated column in the new table.
Calendar = CALENDARAUTO()
weekday = WEEKDAY('Calendar'[Date])YearWeek = YEAR('Calendar'[Date])*100+WEEKNUM('Calendar'[Date])users = CALCULATE(DISTINCTCOUNT('WebSpice Log'[IP]))userswithworkdays = IF('Calendar'[weekday]=7 || 'Calendar'[weekday]=1,BLANK(),'Calendar'[users])2. Then we can create a measure to achieve our goal.
average = CALCULATE(SUM('Calendar'[userswithworkdays]),ALLSELECTED('Calendar'),VALUES('Calendar'[YearWeek]))/5For more details, please check the pbix as attached.
Regards,
Frank
