Dax Measure

[lliu_western]

Hello Community, 

I am really hitting a road block so any help will be really appreciated.

I have a sample table as below:

Name	Hours Worked	Percentage of work done	Month
Bob	2	0.11	January
Amy	1	0.05	February
Bob	3	0.35	January
Tina	4	0.4	March
Bob	3	0.35	January

Taking "Bob" as an example, in the PowerBI, I would have a Pivot table that looks like below: 

(As the example below, it contains a Drill down in rows, Month then Name)

	Total Hours Worked	Percent of work done
January
Bob	8	0.81
Amy	4	0.76
Tina	6	0.73
Eric	7	0.68
Total Hours Work	25

The first measure I need - demostrated as "Calulation A"

Formula for Calculation A:   

(Total Hours worked by Bob * Total Percent of work done by Bob)/Total Hours work by "Everyone")

As for Bob in Calculation A:

(8*0.81)/25

	Total Hours Worked	Percent of work done	Calculation A (A measure)
January
Bob	8	0.81	0.2592
Amy	4	0.76	0.1216
Tina	6	0.73	0.1752
Eric	7	0.68	0.1904
Total Hours Work	25

Here is where I am stuck - The desired table has no names as part of the row drill down but only months but I need the calculation to stay in groups base on its name. 

The most difficult part is, when multilple names are selected (Names is part of the slicer selection), 

the calculation becomes as follow: 

(0.2592*8/25)+(0.1216*4/25)+(0.1752*6)/0.73+0.1904*7/25 = Final Answer

Basically, Calculation A (measure) needs to times the total work hour of that person itself again

then divide the total work hours by everyone

then add all the person's individual result all together

to get my final answer in that work month. 

Sorry, it sounds very complicated and it is very complicated and hard so I am very stuck becasuse I thought I could use group by but group by doesn't work either. Any possible ways to solve this would be highly appreciated.

Thank you!

[amitchandak · Accepted solution]

Try like

sumx(summarize(table,table[name],"_line_sum",sumx(table,table[Total Hours Worked]*table[Percent of work done])
			,"_sum",sumx(all(table),table[Total Hours Worked])),divide([_line_sum],_sum))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin