cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Average on Matrix Subtotals

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]))

 

average on subtotals.png

 

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: 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:

 

screenfromthereport.png

 

Hope somebody could use this in future.

 

 

2 REPLIES 2
v-cherch-msft Super Contributor
Super Contributor

Re: Average on Matrix Subtotals

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Anonymous
Not applicable

Re: 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:

 

screenfromthereport.png

 

Hope somebody could use this in future.

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 220 members 2,287 guests
Please welcome our newest community members: