Wrong Sum totals when using DISTINCTCOUNT multiplied by measures

[Anonymous]

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

[Greg_Deckler]

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: 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

[Anonymous]

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

[Greg_Deckler]

So, I am not going to hand type a Timelog table FYI.

[Anonymous]

^^ i was looking for a file attach button ... uploaded to dropbox now. That's an export from PowerBI from the Matrix visual.

[Greg_Deckler]

I have the first table downloaded but not the Timelog (second table image). Am I missing a link in your post?

[Anonymous]

My bad! I made a zip of the actual tables as the previous sample was already a combination of data from the visual.

sample data (dropbox) 

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!

[Anonymous]

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...