Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Zarlot531
Helper V
Helper V

Sum Column based on _several_ (not just one!) distinct fields and without having to concatenate?

 

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. 

 

Capture133.JPG

2 ACCEPTED SOLUTIONS
Zarlot531
Helper V
Helper V

I figured it out. 

 

GroupedPaidHours = SUMX(summarize(Query2,Query2[Name],Query2[WeekEnd],Query2[PaidHours]),Query2[PaidHours])

View solution in original post

This is actually the solution:

 

GroupedPaidHours = SUMX(summarize(Query2,Query2[Name],Query2[WeekEnd],Query2[Day],Query2[PaidHours]),Query2[PaidHours])

View solution in original post

3 REPLIES 3
Zarlot531
Helper V
Helper V

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])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.