Calculate total hours worked in own and others projects from ''weirdly'' structured data

[tuliosouzaa]

I have a table like this:

Assigned To	Worked By	Worked Hours
Josh	Josh	25
Josh	Maria	26
Phillip	Phillip	48
Phillip	Maria	52
Phillip	Julia	64
Maria	Maria	98
Maria	Josh	70
Maria	Phillip	52
Maria	Andre	30
Maria	Julia	60
Julia	Julia	84
Julia	Maria	50
Julia	Phillip	22
Andre	Andre	12
Andre	Josh	36
Andre	Phillip	47
Andre	Julia	22

And I need to create a visual where I display the support given by a worker (hours worked in others projects) and the support received (hours others worked in their projects).

Considering this dummy data I would have a visual such as:

I tried all kinds of formulas but I could only get to the point where I get the support received. 

If possible (or needed) the result could also be a table such as:

Worker	Worked Hours	Support Received	Support Given
Josh	131	38	106
Phillip	181	116	133
Maria	226	212	128
Julia	230	72	146
Andre	42	105	30

Is it possible to achieve this with the data I'm starting from?

[ryan_mayu · Accepted solution]

@tuliosouzaa 

not sure if the expected of support receive is correct. It's becasue what I get is different from yours

1. try to create a table

Table 2 = DISTINCT('Table'[Assigned To])

don't create relationship

2. create measures

workedhours = sumx(FILTER('Table','Table'[Worked By]=max('Table 2'[Assigned To])),'Table'[Worked Hours])

supported receive = sumx(FILTER('Table','Table'[Assigned To]=max('Table 2'[Assigned To])&&'Table'[Worked By]<>max('Table 2'[Assigned To])),'Table'[Worked Hours])

support given = sumx(FILTER('Table','Table'[Worked By]=max('Table 2'[Assigned To])&&'Table'[Assigned To]<>max('Table 2'[Assigned To])),'Table'[Worked Hours])

pls see the attachment below

[v-kaiyue-msft · Accepted solution]

Hi @tuliosouzaa  ,

@ryan_mayu  provided very correct solution, I'll add how to get the desired visual.
1. Create a metric to get the percentage of support provided.

given % = 'Table'[support given] / 'Table'[workedhours]

2. Create a metric to get the percentage of support received.

received % = 'Table'[supported receive] / 'Table'[workedhours]

3. Create the visual object, put the fields and the final result as shown in the figure below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[ryan_mayu · Accepted solution]

@tuliosouzaa 

not sure if the expected of support receive is correct. It's becasue what I get is different from yours

1. try to create a table

Table 2 = DISTINCT('Table'[Assigned To])

don't create relationship

2. create measures

workedhours = sumx(FILTER('Table','Table'[Worked By]=max('Table 2'[Assigned To])),'Table'[Worked Hours])

supported receive = sumx(FILTER('Table','Table'[Assigned To]=max('Table 2'[Assigned To])&&'Table'[Worked By]<>max('Table 2'[Assigned To])),'Table'[Worked Hours])

support given = sumx(FILTER('Table','Table'[Worked By]=max('Table 2'[Assigned To])&&'Table'[Assigned To]<>max('Table 2'[Assigned To])),'Table'[Worked Hours])

pls see the attachment below

[Bipin-Lala]

Hi @tuliosouzaa,

I hope if you have understood your question correctly, you can do a couple of transformations in the query editor to achieve the final table expected by you.

The end result looks something like this - 

I was able to achieve this by creating 2 reference tables from the "weirdly structured" input table, one table for Assigned hours and one for Worked Hours. Later I merged back these 2 tables on the person name, to bring the data together and visualize it in the report. I am attaching the link to the pbix file for you to review the steps.

Hours_Worked pbix

Do let me know how it goes!