Average Number of People per Day of Week

[markbell]

Hi all,

I have a list of people names along with the dates that they are onsite for (I work for a mining company). What I would like to do is summarize this data by calculating the average number of people onsite for each and every day (ie Sun, Mon, ...) over a filtered timeframe.

My data looks something like this:

I would like to produce a graph that shows the average number of people onsite for each day of the week with filters (slicers) below it for Year, Quarter, Month, etc

I'm new to Power BI and am excited at the possibilities! 🙂

Many thanks in advance.

[samdthompson · Accepted solution]

OK, welcome aboard.

First you will need a date table to join to. Once you do, any of your time based functions should refer to that. Make sure your date table has all those dimensions you want.

Secondly you will need to write a measure. I suspect you will need something like:

      Head_count=countrows(table)

this will give you the total number of people on site which once sliced by day etc will give you a per day number.

The sky is the limit with what you might then want to do. You might want people visiting YTD (i did when i was at a mine). The answer is to use the date table eg: Total_calendar_YTD =TOTALYTD(Head_count,datetbl[date])

// If this solves your question please mark as solved

[Vvelarde · Accepted solution]

To create a date table you can use this sample:

In Modeling--New Table

DateTable =
ADDCOLUMNS (
CALENDAR ( "01/01/2000";"31/12/2017");
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" )
)

[Vvelarde · Accepted solution]

To create a date table you can use this sample:

In Modeling--New Table

DateTable =
ADDCOLUMNS (
CALENDAR ( "01/01/2000";"31/12/2017");
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" )
)

[markbell]

Hi all - thank you.

I have successfully created a date table using the following:

tblDate = ADDCOLUMNS(CALENDAR("1-jan-2000","31-dec-2025"),"DateAsInteger",format([Date],"YYYYMMDD"),"Year",year([Date]), "Monthnumber", FORMAT ( [Date], "MM" ),"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),"MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ),"DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ),"DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ),"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

and have also created the Total_calendar_YTD measure and graphed it. I am still struggling to work out how to average the number of people that are onsite for each and every day of the week. Do you have any formulas for that?

Thanks again.

[samdthompson]

Average_head=AVERAGEX(Table,COUNTROWS(Table))

// If this solves your question please mark as solved

[markbell]

Hi samdthompson

With the dataset that I have, the Average_head function returns the same number as the Total_calendar_YTD.

[samdthompson · Accepted solution]

if you chart against the day of the week then you will have the average for the day etc. The total will be the total.

// If this solves your question please mark as solved

[markbell]

Do you have any screenshots that you can post as I'm getting completely lost now. 😞

[markbell]

Hi all,

I've managed to get it working using the following measure:

Avg_Count = DIVIDE(COUNTROWS(onsite),DISTINCTCOUNT(Onsite[RosterDate]))

Thanks for everyone's help.

[samdthompson · Accepted solution]

OK, welcome aboard.

First you will need a date table to join to. Once you do, any of your time based functions should refer to that. Make sure your date table has all those dimensions you want.

Secondly you will need to write a measure. I suspect you will need something like:

      Head_count=countrows(table)

this will give you the total number of people on site which once sliced by day etc will give you a per day number.

The sky is the limit with what you might then want to do. You might want people visiting YTD (i did when i was at a mine). The answer is to use the date table eg: Total_calendar_YTD =TOTALYTD(Head_count,datetbl[date])

// If this solves your question please mark as solved