- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
Average on Matrix Subtotals
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
11-01-2018 03:16 AM
Hello, Power BI Experts!
Just couldn't find through the same questions on the community the solution that might help me. Will appriciate any help!
I need to calculated average on column subtotals.
User Name is on Rows, Time Entry Year Month is on Columns.
My measure on Values AverageWeeklyHours is calculated as a sum of logged hours in a month for the user divided by the number of weeks in this months.
The number of weeks is not a whole number because it's calculated as the number of days in a months divided by 7.
TotalHoursLoggedInAMonth = CALCULATE(SUM(TT_TimeEntries[Duration]), ALLEXCEPT(TT_TimeEntries, TT_TimeEntries[User Name], TT_TimeEntries[Time Entry Year Month]))
NumberOfWeeksInAMonth = TT_TimeEntries[NumberOfDaysInAMonth] / 7
AverageWeeklyHours = DIVIDE([TotalHoursLoggedInAMonth], AVERAGE(TT_TimeEntries[NumberOfWeeksInAMonth]))
I tried several methods but couldn't get the right AVG for the selected number of months.
For the first user it should be 44.33, for the second = 3.64 and so on...
Maybe I missed something in my calculations.
Thank you in advance!
Solved! Go to Solution.
Accepted Solutions
Re: Average on Matrix Subtotals
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
11-06-2018 12:30 AM - edited 11-06-2018 12:31 AM
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.
All Replies
Re: Average on Matrix Subtotals
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
11-01-2018 07:21 PM
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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Re: Average on Matrix Subtotals
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
11-06-2018 12:30 AM - edited 11-06-2018 12:31 AM
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.