cancel
Showing results for
Did you mean:
Member

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

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

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

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

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

Member

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

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!