cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mhbon
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:

 

DepartmentTaskTask2Employee01-202102-2021
AccountingCount moneyCount billsScrooge McDuck25%...
AccountingCount moneyCount changeScrooge McDuck25%...
AccountingPull a prankPrank the nephewsScrooge McDuck25%...
MischiefPull a prankPrank Uncle ScroogeHuey Duck100%...
MischiefPull a prankPrank Uncle ScroogeDewey Duck25%...
MischiefPull a prankPrank Uncle ScroogeLouie Duck50%...

 

The data is formatted in the following way:

 

DepartmentTaskTask2EmployeeDateHours
AccountingCount moneyCount billsScrooge McDuck2021-01-223
AccountingCount moneyCount changeScrooge McDuck2021-01-223
AccountingGo on an adventureFinding treasureScrooge McDuck2021-01-233
MischiefPull a prankPrank Uncle ScroogeHuey Duck2021-01-223
MischiefSleepingGood night sleepDewey Duck2021-01-229
MischiefPull a prankPrank Uncle ScroogeDewey Duck2021-01-223
MischiefPull a prankPrank Uncle ScroogeLouie Duck2021-01-226
AccountingPull a prankPrank the nephewsScrooge McDuck2021-01-223
MischiefPlayPlay with toysLouie Duck2021-01-216

 

When the matrix is collapsed, the results shoud still add up:

 

Department01-202102-2021
Accounting75%...
Mischief44%...

 

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
rfigtree
Resolver II
Resolver II

mHours:=SUM(Table1[Hours])

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

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

 

use m% in your pivot table.

Capture.JPG

View solution in original post

2 REPLIES 2
rfigtree
Resolver II
Resolver II

mHours:=SUM(Table1[Hours])

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

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

 

use m% in your pivot table.

Capture.JPG

View solution in original post

TarunSharma
Resolver III
Resolver III

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors