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.
So below is some payroll data from a dataset (obviously the name has been left off).
What I'd like to do is have a weekly trend analysis showing on-site % time of paid hours. The problem is that I want to keep the details for drill-down, so for example below I show 1/29/2019, part of the 2/3/19 week end, for this particular employee had 11.48 paid hours. The % I'm after is (6.71+3.8) / 11.48. But I need to sum the 6.71 and 3.8 but not sum the 11.48. So really I need to sum PaidHours by unique EmployeeID and Week End date. Or I guess I could run an average. SumX with Distinct doesn't seem to work here because distinct only allows (from what I understand) the whole table or one column. Is there a way to do this without making a concacenated field? I might try somehow using average instead of sum here in a second.
Solved! Go to Solution.
I figured it out.
GroupedPaidHours = SUMX(summarize(Query2,Query2[Name],Query2[WeekEnd],Query2[PaidHours]),Query2[PaidHours])
This is actually the solution:
GroupedPaidHours = SUMX(summarize(Query2,Query2[Name],Query2[WeekEnd],Query2[Day],Query2[PaidHours]),Query2[PaidHours])
I figured it out.
GroupedPaidHours = SUMX(summarize(Query2,Query2[Name],Query2[WeekEnd],Query2[PaidHours]),Query2[PaidHours])
Actually, my solution won't always work because it groups "PaidHours" in, but I can't seem to use this formula without including what i'm summing by in the group...odd
This is actually the solution:
GroupedPaidHours = SUMX(summarize(Query2,Query2[Name],Query2[WeekEnd],Query2[Day],Query2[PaidHours]),Query2[PaidHours])
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |