Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 =
Solved! Go to 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 , 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])
@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.
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.
@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?
Hi @newpi
Group Avg Hours/Day =
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? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |