Group By with some data split over categories

[Jonas_Bertels]

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

[amitchandak]

@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

[Jonas_Bertels]

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 %).

[dax · Accepted solution]

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 Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.