Forum Discussion
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 ID | Duration |
| Project 1 | 3 |
| Project 2 | 3 |
| Project 3 | 7 |
Table 2 is a detailed list of user durations in the projects. (Sample Below)
| Detail Table | ||
| Project ID | UserName | Duration |
| Project 1 | Jordan | 1.5 |
| Project 1 | Justin | 1.5 |
| Project 2 | Rory | 1 |
| Project 2 | Dustin | 1 |
| Project 2 | Ian | 1 |
| Project 3 | Sergio | 7 |
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 Projects | Average Duration | Projects Above Average | Projects Below Average |
| 3 | 4.33 | 1 | 2 |
Visual 2
| Users | Average Project Duration | Difference |
| Jordan | 4.33 | -2.83 |
| Justin | 4.33 | -2.83 |
| Rory | 4.33 | -3.33 |
| Dustin | 4.33 | -3.33 |
| Ian | 4.33 | -3.33 |
| Sergio | 4.33 | 2.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?
Anonymous
pls see the attachment below
