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 there,
I have found a few posts about how people expect the row totals to be the sum of the rows but how it isn't and how it is technically correct and not a bug, still I was not able toget any of the provided solutions to work. I tried SUMX, Calculate without success.
Maybe I am missing something...
My measures:
WHRS = Work Schedule hrs per day per employee (calculated column)
RWD = Days where time was reported
RWD_HRS = WHRS * RWD for any given period of time, e.g. week 25
WHRS is a calculated Column in a Userlist table and Timelog data is ina Timelog table. The measures reside in the timelog table.
UR = utilization rate based off the reported time divided by the 100%, i.e. work time (WHRS*RWD)
RWD = DISTINCTCOUNT('Timelog (ALL)'[Timelog.trackedDate])
RWD_HRS = RWD*SUM('Contact (User list)'[WHRS])
WHRS = Switch( 'Contact (User list)'[Contact.lastName],"name1",8.75,"name2",8.75,"name3",7,"name4",3.5,7.4)
is adding the work time as decimal per defined user and 7.4 hrs for all the rest in the user table.
UR = SUM('Timelog (ALL)'[Timelog.hours]) / RWD_HRS)
Measures work fine on the individual rows as pointed out by many of the posts, however the row totals are not working as expected.
Table totals for week 25, top line is the one that does the hrs correctly as they are part of the timelog table and not a measure. the rest are measures and only work on the individual rows.
RWD_HRS total for the group is shown as 362 and RWD as 7 where as if I pivot the data it will just sum the individual rows and show RWD_HRS as 259 and RWD as 35.
I found this post in particular that offers a very easy way out... by switching off the row totals... since the individual totals are correct... however... I need to show the sum for the groups. How?
EDIT: I use DISTINCTCOUNT since I only want each date of time reported once to determine if an employee has worked on that day, as there could be several entries for one date. Maybe there is a better way to determine the number of days worked per employee?
Any help and pointers are much appreciated. Thanks for reading!
Cheers,
Alexander
Any chance you can post sample/example data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The answer often involves a combination of HASONEVALUE/HASONEFILTER and ALLSELECTED. Check out my MM3TR&R Quick Measure here:
Some sample data:
-> dropbox: sample_data.xlsx
Teams | Employee Name | Hrs | RWD_HRS | RWD | UR | Week | Day |
team1 | name1 | 7.75 | 7.4 | 1 | 104.73% | 25 | 18 |
team1 | name1 | 5.58 | 7.4 | 1 | 75.45% | 25 | 19 |
team1 | name1 | 6.5 | 7.4 | 1 | 87.84% | 25 | 20 |
team1 | name1 | 6.67 | 7.4 | 1 | 90.09% | 25 | 21 |
team1 | name1 | 6.5 | 7.4 | 1 | 87.84% | 25 | 22 |
team1 | name2 | 7 | 7.4 | 1 | 94.59% | 25 | 19 |
team1 | name2 | 6.5 | 7.4 | 1 | 87.84% | 25 | 20 |
team1 | name2 | 6 | 7.4 | 1 | 81.08% | 25 | 21 |
team1 | name2 | 6.5 | 7.4 | 1 | 87.84% | 25 | 22 |
team1 | name3 | 5.5 | 7.4 | 1 | 74.32% | 25 | 18 |
team1 | name3 | 5.5 | 7.4 | 1 | 74.32% | 25 | 19 |
team1 | name3 | 7 | 7.4 | 1 | 94.59% | 25 | 20 |
team1 | name3 | 6 | 7.4 | 1 | 81.08% | 25 | 21 |
team1 | name3 | 5 | 7.4 | 1 | 67.57% | 25 | 22 |
team1 | name3 | 5 | 7.4 | 1 | 67.57% | 25 | 23 |
team1 | name4 | 13 | 7.4 | 1 | 175.68% | 25 | 17 |
team1 | name4 | 10 | 7.4 | 1 | 135.14% | 25 | 18 |
team1 | name4 | 5.75 | 7.4 | 1 | 77.70% | 25 | 21 |
team1 | name4 | 5.75 | 7.4 | 1 | 77.70% | 25 | 22 |
team1 | name5 | 6.25 | 7.4 | 1 | 84.46% | 25 | 18 |
team1 | name5 | 6.58 | 7.4 | 1 | 88.96% | 25 | 19 |
team1 | name5 | 6 | 7.4 | 1 | 81.08% | 25 | 20 |
team1 | name5 | 6.5 | 7.4 | 1 | 87.84% | 25 | 21 |
team1 | name5 | 5.33 | 7.4 | 1 | 72.07% | 25 | 22 |
team1 | name6 | 2.8 | 7.4 | 1 | 37.84% | 25 | 18 |
team1 | name6 | 0.17 | 7.4 | 1 | 2.25% | 25 | 19 |
team1 | name6 | 6.6 | 7.4 | 1 | 89.19% | 25 | 20 |
team1 | name6 | 2.48 | 7.4 | 1 | 33.56% | 25 | 21 |
team1 | name6 | 2.17 | 7.4 | 1 | 29.28% | 25 | 22 |
team1 | name7 | 8 | 7.4 | 1 | 108.11% | 25 | 18 |
team1 | name7 | 7.42 | 7.4 | 1 | 100.23% | 25 | 19 |
team1 | name7 | 7.92 | 7.4 | 1 | 106.98% | 25 | 20 |
team1 | name7 | 5.33 | 7.4 | 1 | 72.07% | 25 | 21 |
team1 | name7 | 3.83 | 7.4 | 1 | 51.80% | 25 | 22 |
team1 | name7 | 4.33 | 7.4 | 1 | 58.56% | 25 | 23 |
team2 | name8 | 7.5 | 7.4 | 1 | 101.35% | 25 | 18 |
team2 | name9 | 7.5 | 7.4 | 1 | 101.35% | 25 | 19 |
team2 | name9 | 7.5 | 7.4 | 1 | 101.35% | 25 | 20 |
team2 | name9 | 6 | 7.4 | 1 | 81.08% | 25 | 21 |
team2 | name9 | 5 | 7.4 | 1 | 67.57% | 25 | 22 |
team2 | name9 | 5.5 | 7 | 1 | 78.57% | 25 | 18 |
Timelog table (relation with contact list table via userid column)
In essence with DISTINCTCOUNT on the .trackedDate field I am determining the number of days an employee has worked and then multiply it with the work schedule hours associate with that employee from the userlist table.
Edit: adding relationship: Contact.id <--> Timelog.userId
So, I am not going to hand type a Timelog table FYI.
^^ i was looking for a file attach button ... uploaded to dropbox now. That's an export from PowerBI from the Matrix visual.
I have the first table downloaded but not the Timelog (second table image). Am I missing a link in your post?
My bad! I made a zip of the actual tables as the previous sample was already a combination of data from the visual.
The zip contains these tables
1) Timelog
2) Contact teams
3) Contact User List
Relationships:
Contact Teams .memberIds *---<>---1 Contact User list .Contact.Id
Contact User List .Contact.ID 1---<>---* Timelog .userID
All Measures are currently created within the Timelog table in PowerBI (RWD/RWD_HRS/UR).
Thanks for your time! Much appreciated!
I tried a few more times without luck. I am beginning to think I might need a "helper" table where I "duplicate" the days worked info and the sum of time worked for a day there and just put the work time per day also there for each day... maybe...
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |