cancel
Showing results for
Did you mean:
Frequent Visitor

## SUM of distinct rows using more filters

Hi everybody,

I'm just getting started with power BI and there is one (probably simple) thing I just can't figure out.

Let's say I have a table with three columns (User, Spent time and Project category)

I need to add one column for every row, where total spent time of particular User (except Absence as Project category) should be.

That means that for getting the right number into the new column I need to find the User in the row, find all of the rows with this User except those, where "Absence" is in column "Project category" and sum those values in column "Spent time" and get this number to every row according to the User in the new Column.

Any ideas for the formula? Thanks

1 ACCEPTED SOLUTION
Super User

In Query Editor, have you tried:

1. Filter Project Column to exclude Absense, then

3. Choose 'Group By' to SUM the hours

Does this help?

Raw Data:

Filter applied to exclude C:

Resulting GROUP BY still excludes C

Please give Kudos or Mark as a Solution!

Proud to be a Super User helping give back to the community!
Thank You!

6 REPLIES 6
Community Support

HI @Murkow,

It will be help if you share some sample data to test.
In addition, if you want to sum of the absence time of each user, you can try to use below formula.

`User Total Absence Time= CALCULATE(SUM(Table[Spent time]),FILTER(ALL(Table),[User]=EARLIER([User])&&[Project category]="Absence"))`

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

Let's say I have this table. I want to add a new column, in which a value of the spent time on all of the Project categories except "Absence" for the particular user will be calculated.

Super User

How's this?

SumExclAb = CALCULATE(SUM(Table1[Hours]),     // SUM Hours with further Calcuations

FILTER(Table1,Table1[Category] <> "Absence"))      // Add FILTER so you can exclude your desired Category

Please give Kudos or Mark as a Solution!

Proud to be a Super User helping give back to the community!
Thank You!

Frequent Visitor

Hi,

thanks, but not really what I'm looking for. I need a new column, where the sum of all Project categories except Absence for that User will be.

If there is User 3 ten times, I need the same value for this user in every his row in the new column

Super User

In Query Editor, have you tried:

1. Filter Project Column to exclude Absense, then

3. Choose 'Group By' to SUM the hours

Does this help?

Raw Data:

Filter applied to exclude C:

Resulting GROUP BY still excludes C

Please give Kudos or Mark as a Solution!

Proud to be a Super User helping give back to the community!
Thank You!

Frequent Visitor

Hi everybody,

I'm just getting started with power BI and there is one (probably simple) thing I just can't figure out.

Let's say I have a table with three columns (User, Spent time and Project category)

I need to add one column for every row, where total spent time of particular User (except Absence as Project category) should be.

That means that for getting the right number into the new column I need to find the User in the row, find all of the rows with this User except those, where "Absence" is in column "Project category" and sum those values in column "Spent time" and get this number to every row according to the User in the new Column.

Any ideas for the formula? Thanks

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.