Forum Discussion

lliu_western's avatar
lliu_western
Frequent Visitor
6 years ago
Solved

Dax Measure

Hello Community, 

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

 

I have a sample table as below:

NameHours WorkedPercentage of work doneMonth 
Bob20.11January
Amy10.05February
Bob30.35January
Tina40.4March
Bob30.35January

 

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 WorkedPercent of work done
January   
Bob80.81
Amy40.76
Tina60.73
Eric70.68
Total Hours Work25 

 

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 WorkedPercent of work done

Calculation A

(A measure)

January    
Bob80.810.2592
Amy40.760.1216
Tina60.730.1752
Eric70.680.1904
Total Hours Work25  

 

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!

 

 
 

1 Reply