Dax for total percentage of type

[mhbon]

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! 

[rfigtree · Accepted solution]

mHours:=SUM(Table1[Hours])

mTotalHours:=CALCULATE([mHours],ALLEXCEPT(Table1,Table1[Date]))

m%:=DIVIDE([mHours],[mTotalHours])

use m% in your pivot table.

[TarunSharma]

Hello @mhbon 
After transformation I can't see the percentage values. Is data is missing in the above table?