Calculating Average Weekday Users per week

[Matthew_Theis]

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  

[v-frfei-msft · Accepted solution]

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]))/5

For more details, please check the pbix as attached.

Regards,

Frank

[Matthew_Theis]

Here is a link to the .pbix.  Hopefully this will work.  I don't see a Choose File option as mentioned here. 

https://kemet-my.sharepoint.com/:u:/p/matthewtheis/ET6V5bSCcShLoG5bIy_VvPIBeWaL0a_YclJWb_V1gi3Yyw?e=...

[Greg_Deckler]

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