I have a table that contains the daily roster for each staff
I have a measure that calculates the daily count
Staff rostered = CALCULATE( DISTINCTCOUNTNOBLANK('Staff rosters'[staff_key]), FILTER('Staff rosters', 'Staff rosters'[Rostered type] = "Contact" ) )
enter image description here
I want to use the measure to display monthly average per centre when selected a centre, and average across the organisation when nothing is selected. How can I do that? When I use the measure with the month-year, then I get total staff rostered over the month not an average. It is okay when I am looking at organization wide
I have created a summarized table
Rostered Contact Staff = CALCULATETABLE( SUMMARIZE( 'Staff rosters', 'Staff rosters'[Date rostered], 'Staff rosters'[centre_key], "Staff Count", DISTINCTCOUNTNOBLANK('Staff rosters'[staff_key]), "Month", FORMAT('Staff rosters'[Date rostered], "MMM-YYYY") ), 'Staff rosters'[Rostered type] = "Contact" )
I have a measure in the summarized that does the trick
`Average Count =
VAR AvgCount = AVERAGE(‘Rostered Contact Staff'[Staff Count])
RETURN
IF (
HASONEVALUE(Organisation[Kindergartens]),
AvgCount,
SUMX(
VALUES(Organisation[Kindergartens]),
AvgCount)
)`
I am keen not to create a separate table and do this within the table
Vivek Sharma is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.