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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors