cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Zarlot531 Resolver I
Resolver I

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

Accepted Solutions
Zarlot531 Resolver I
Resolver I

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

I figured it out. 

 

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

View solution in original post

Highlighted
Zarlot531 Resolver I
Resolver I

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

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 Resolver I
Resolver I

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

I figured it out. 

 

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

View solution in original post

Zarlot531 Resolver I
Resolver I

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

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

Highlighted
Zarlot531 Resolver I
Resolver I

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

This is actually the solution:

 

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

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors