cancel
Showing results for
Did you mean:
Frequent Visitor

## Dax for total percentage of type

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!

1 ACCEPTED SOLUTION
Resolver II

mHours:=SUM(Table1[Hours])

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

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

use m% in your pivot table.

2 REPLIES 2
Resolver II

mHours:=SUM(Table1[Hours])

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

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

use m% in your pivot table.

Resolver III

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

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group