Hi all,
I'll had a search but can't find an answer for my exact situation so hoping someone can help me out. I have two measures that calculate total hours depending on critera:
SW Total = CALCULATE([Total Hours],'Actual Services'[Provider Class] = "SWK",'Actual Services'[External Provider]="n")
AGC Total = CALCULATE([Total Hours],'Actual Services'[Provider Class] = "AGC", 'Actual Services'[External Provider] ="n")
The from these I've created another measure:
Total Service Hours = [SW Total]+[AGC Total]
Basically this gives me all hours worked that by Support Worker (SWK) and Agency (AGC) but doesn't include Brokerage (External Provider)
I have then mapped this by role and months of the year:
What I'm trying to do now is have a column at the end that shows the average per month by role, i.e. total by role for Jan - Aug /7, but with a measure that will up date itself as more data comes in for future months Sept, Oct etc.
I can't use any calculations that have a table reference because the data I need the average for is coming from a measure not a column. Any ideas? thanks,
@eburke wrote:
Hi all,
I'll had a search but can't find an answer for my exact situation so hoping someone can help me out. I have two measures that calculate total hours depending on critera:
SW Total = CALCULATE([Total Hours],'Actual Services'[Provider Class] = "SWK",'Actual Services'[External Provider]="n")
AGC Total = CALCULATE([Total Hours],'Actual Services'[Provider Class] = "AGC", 'Actual Services'[External Provider] ="n")
The from these I've created another measure:
Total Service Hours = [SW Total]+[AGC Total]
Basically this gives me all hours worked that by Support Worker (SWK) and Agency (AGC) but doesn't include Brokerage (External Provider)
I have then mapped this by role and months of the year:
What I'm trying to do now is have a column at the end that shows the average per month by role, i.e. total by role for Jan - Aug /7, but with a measure that will up date itself as more data comes in for future months Sept, Oct etc.
I can't use any calculations that have a table reference because the data I need the average for is coming from a measure not a column. Any ideas? thanks,
I don't get what you mean average, if it means total/row count, you can try
avg =
DIVIDE (
CALCULATE ( [Total Service Hours], ALLEXCEPT ( 'table', 'table'[Role] ) ),
CALCULATE (
DISTINCTCOUNT ( 'table'[Role] ),
ALLEXCEPT ( 'table', 'table'[Role] )
)
)
If it is not your case, please post some sample data and expected output.
Hi, thanks what I'm looking for is the average of the totals across the number of months (current and going forward), so currently that would mean the total of each role by month divided by the number of months in the visual (which will change as time goes on. For example currently for Maintenance in excel the formula would be taking the total for all months (815.00) and dividing it by the number of months (8) = Average monthly total = 101.88. I need this showing as just one entry at the end of the visual like the Total Service Hours currently does.
I've tried the formula you suggested but it is trying to average each month individually which just gives the same result as the monthly total. Thanks,
