Forum Discussion
Average on Matrix Subtotals
hi, v-cherch-msft, thank you for your reply.
I tried ALLSELECTED() and it turned out that this is not my case.
But hopefully I found a solution.
I recalculated Average Number of Weeks in a Month:
TotalHours = SUM(TT_TimeEntries[Duration])
NumberOfWeeks = AVERAGEX(TT_TimeEntries, [NumberOfDaysInAMonth] / 7)
And created a final measure as:
AverageHoursInAWeek =
AVERAGEX (
VALUES ( 'TT_TimeEntries'[Time Entry Year Month] ),
CALCULATE ( DIVIDE ( [TotalHours], [NumberOfWeeks]) )
)The final result is:
Hope somebody could use this in future.
Hi Anonymous
It seems you may try to use ALLSELECTED Function. Below is the article for your reference. If it is not your case, It's better that if you could share some simplified data which could reproduce your scenario and your desired output.
https://www.sqlbi.com/articles/understanding-allselected/
Regards,
Cherie
hi, v-cherch-msft, thank you for your reply.
I tried ALLSELECTED() and it turned out that this is not my case.
But hopefully I found a solution.
I recalculated Average Number of Weeks in a Month:
TotalHours = SUM(TT_TimeEntries[Duration])
NumberOfWeeks = AVERAGEX(TT_TimeEntries, [NumberOfDaysInAMonth] / 7)
And created a final measure as:
AverageHoursInAWeek =
AVERAGEX (
VALUES ( 'TT_TimeEntries'[Time Entry Year Month] ),
CALCULATE ( DIVIDE ( [TotalHours], [NumberOfWeeks]) )
)The final result is:
Hope somebody could use this in future.