Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |