cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

Avg calculation showing unselected rows & their blank values

I'm using an avg formula to calculate the avg of avg. It may not be the best way but that is the requirement at the moment. My problem is when I select a group of users out of a larger group, it is also showing me other users but with all blank values except in the group avg column. My data looks like below:

 

User nameTotal HoursTotal DaysUser Avg hours/dayGroup avg Hours/day
U 18187
U 230567
U 3   7
U 4   7
U 5   7

 

Group Avg Hours/Day = 

CALCULATE(AVERAGEX(ALLSELECTED('Tabel Name'[User Name]),[Avg Hours / Day]))
 
U1 & U2 are part of group A which I'm selecting though a slicer.
Avg of group A is (Avg hours U1+ Avg hours U2)/2
 
I want my table to not show other users which are not a part of this group (U3 to U5)
1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

@newpi , Need to try like

CALCULATE(AVERAGEX(values('Tabel Name'[User Name]),[Avg Hours / Day]))

or

AVERAGEX(summarize('Tabel Name','Tabel Name'[User Name],"_1",[Avg Hours / Day]),[_1])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

6 REPLIES 6
Community Champion
Community Champion

Hi @newpi 

 

Group Avg Hours/Day = 

CALCULATE(AVERAGE('Tabel Name',[Avg Hours / Day],ALLEXPECT('Tabel Name','Tabel Name'[User Name])))
 

Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!

 

Regards,
Pranit


Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.


Appreciate your Kudos, Press the thumbs up button!!👍


Regards,
Pranit


Super User IV
Super User IV

@newpi , Need to try like

CALCULATE(AVERAGEX(values('Tabel Name'[User Name]),[Avg Hours / Day]))

or

AVERAGEX(summarize('Tabel Name','Tabel Name'[User Name],"_1",[Avg Hours / Day]),[_1])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@amitchandak The second formula works great.

AVERAGEX(summarize('Tabel Name','Tabel Name'[User Name],"_1",[Avg Hours / Day]),[_1])

However, If I apply it to a line and stacked column chart, it is showing me individual averages instead of team avgas my columns value has indvidual avg.  Attached screenshot. Blue line is the avg which should be 7.07 for both in this case.Screen Shot 2020-08-13 at 10.02.52 PM.png 

@pranit828 the table I showed in my example is the output and except Total hours, all other columns are DAX measures. So can't use Average or ALLEXCEPT in this case. 

@amitchandak Would you be able to help here with the Column line and stacked view and the average formula?

Community Support
Community Support

Hi @newpi ,

You can modify the measure like this:

Group avg hours/day =
VAR tab =
    SUMMARIZE ( ALL ( 'Table' ), 'Table'[User name], "_1", [User Avg hours/day] )
RETURN
    IF ( NOT ( ISBLANK ( SUM ( 'Table'[Total Hours] ) ) ), AVERAGEX ( tab, [_1] ) )

avergae.png

Attached a sample file that hopes to help you: average.pbix

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors