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.
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.
Solved! Go to 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
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" )
)
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
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" )
)
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.
Average_head=AVERAGEX(Table,COUNTROWS(Table))
// If this solves your question please mark as solved
Hi samdthompson
With the dataset that I have, the Average_head function returns the same number as the Total_calendar_YTD.
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
Do you have any screenshots that you can post as I'm getting completely lost now. 😞
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.
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
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |