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

Count above/below a Fixed Average

Good evening all!

 

I have a problem i am struggling to solve.

 

I have 2 tables. Table 1 is a list of projects and their total duration. (Sample below)

Master Project
Project IDDuration
Project 13
Project 23
Project 37

Table 2 is a detailed list of user durations in the projects. (Sample Below)

Detail Table
Project IDUserNameDuration
Project 1Jordan1.5
Project 1Justin1.5
Project 2Rory1
Project 2Dustin1
Project 2Ian1
Project 3Sergio7

I need to create 2 visuals from this. 1, the count of projects above the Average duration, and the count below. And 2, the difference each users interaction is against the fixed Project average.  (expected results below)

 

Visual 1

# of ProjectsAverage DurationProjects Above AverageProjects Below Average
34.3312

Visual 2

UsersAverage Project DurationDifference
Jordan4.33-2.83
Justin4.33-2.83
Rory4.33-3.33
Dustin4.33-3.33
Ian4.33-3.33
Sergio4.332.67

 

 

I can get it to show the count when its up against the individual Project IDs, but when i try to do it as a summarized number, it just comes up blank.    Any ideas?

 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@TO_CB 

pls see the attachment below

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

@TO_CB 

pls see the attachment below

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Brilliant!  

 

You showed me my problem!  I was using countrows as well, however, when using FILTER under Calculate, I never thought to just have the Value of duration... I kept doing SUM!

 

Thank you so much!

 

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.