cancel
Showing results for
Did you mean:
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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
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])`
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])`
3 REPLIES 3
Highlighted
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])`
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

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

Announcements

#### 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!

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

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.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors