Forum Discussion
Group By with some data split over categories
Hi all,
I have a data set with time entries. Amongst other things, they need to fill in hours worked & project for each day. I have then grouped these projects into 3 project category.
What I would like to do is to group the time entries by Employee and then show the time worked, but split over the project catogeries. Can someone help me out on how to transform the table?
To help visualize the problem:
| Person | Hours | Project | Project type |
| A | 8 | 1 - client a | 1 |
| B | 8 | 1 - client a | 1 |
| A | 6 | 2 - bussiness development | 2 |
| A | 2 | 1 - client a | 1 |
| B | 8 | 3 - internal | 3 |
| C | 8 | 1 - client C | 1 |
| C | 8 | 1 - client a | 1 |
I would like to get someting in the trend of the following table:
| Person | Time - projecttype1 | Time - projecttype2 | Time - projecttype3 |
| A | 10 | 6 | 0 |
| B | 8 | 0 | 8 |
| C | 16 | 0 | 0 |
If someone could help me achieve this, it would be wonderful. Thanks already.
Kind regards,
Jonas
Hi Jonas_Bertels ,
If you want to show 0 in matrix, you could refer to below table in my sample, if not, you could refer to first table in my sample.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Jonas_Bertels , if you take a matrix and put a person on row, project type on column and hours on values (Take summarization as sum)
It should work
If you need exact text then create a new column like
New Project Type = "Time - projecttype " & [Project Type]
Use this on column
Hi Amitchandak,
Thank you for this solution, it does work. As a follow-up question, is it possible to add 2 columns to this matrix? One where we take the percentage of type 1 hours and type 2 hours over all hours (type 1+2+3) (in %), and one where we take the type 1 hours over the total hours (in %).
Hi Jonas_Bertels ,
If you want to show 0 in matrix, you could refer to below table in my sample, if not, you could refer to first table in my sample.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
