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.
Hi everyone
My company wants me to create a table that tracks wich percentage of time is spend doing specific tasks as a percentage of all the work done. It has to look something like this:
Department | Task | Task2 | Employee | 01-2021 | 02-2021 |
Accounting | Count money | Count bills | Scrooge McDuck | 25% | ... |
Accounting | Count money | Count change | Scrooge McDuck | 25% | ... |
Accounting | Pull a prank | Prank the nephews | Scrooge McDuck | 25% | ... |
Mischief | Pull a prank | Prank Uncle Scrooge | Huey Duck | 100% | ... |
Mischief | Pull a prank | Prank Uncle Scrooge | Dewey Duck | 25% | ... |
Mischief | Pull a prank | Prank Uncle Scrooge | Louie Duck | 50% | ... |
The data is formatted in the following way:
Department | Task | Task2 | Employee | Date | Hours |
Accounting | Count money | Count bills | Scrooge McDuck | 2021-01-22 | 3 |
Accounting | Count money | Count change | Scrooge McDuck | 2021-01-22 | 3 |
Accounting | Go on an adventure | Finding treasure | Scrooge McDuck | 2021-01-23 | 3 |
Mischief | Pull a prank | Prank Uncle Scrooge | Huey Duck | 2021-01-22 | 3 |
Mischief | Sleeping | Good night sleep | Dewey Duck | 2021-01-22 | 9 |
Mischief | Pull a prank | Prank Uncle Scrooge | Dewey Duck | 2021-01-22 | 3 |
Mischief | Pull a prank | Prank Uncle Scrooge | Louie Duck | 2021-01-22 | 6 |
Accounting | Pull a prank | Prank the nephews | Scrooge McDuck | 2021-01-22 | 3 |
Mischief | Play | Play with toys | Louie Duck | 2021-01-21 | 6 |
When the matrix is collapsed, the results shoud still add up:
Department | 01-2021 | 02-2021 |
Accounting | 75% | ... |
Mischief | 44% | ... |
I have the matrix containing Department, Task 1 & 2 and employee in the rows and date in the colums. I just can't seem to get to the right measure. Could you help me out?
Thanks everyone!
Solved! Go to Solution.
mHours:=SUM(Table1[Hours])
mTotalHours:=CALCULATE([mHours],ALLEXCEPT(Table1,Table1[Date]))
m%:=DIVIDE([mHours],[mTotalHours])
use m% in your pivot table.
mHours:=SUM(Table1[Hours])
mTotalHours:=CALCULATE([mHours],ALLEXCEPT(Table1,Table1[Date]))
m%:=DIVIDE([mHours],[mTotalHours])
use m% in your pivot table.
Hello @mhbon
After transformation I can't see the percentage values. Is data is missing in the above table?
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
32 | |
30 | |
18 | |
18 |