cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Zarlot531 Member
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. 

 

Capture133.JPG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Zarlot531 Member
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])

View solution in original post

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

View solution in original post

3 REPLIES 3
Zarlot531 Member
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])

View solution in original post

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

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

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors