Avg calculation showing unselected rows & their blank values

[newpi]

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 name	Total Hours	Total Days	User Avg hours/day	Group avg Hours/day
U 1	8	1	8	7
U 2	30	5	6	7
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)

[amitchandak · Accepted solution]

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

[newpi]

@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. 

[v-yingjl]

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

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.

[newpi]

@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. 

[newpi]

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

[pranit828]

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