cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Murkow
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

In Query Editor, have you tried:

1. Filter Project Column to exclude Absense, then 

2. Highlight your User Column

3. Choose 'Group By' to SUM the hours

 

Does this help?

 

Raw Data:

Capture.PNG

 

Filter applied to exclude C:

Capture2.PNG

 

Resulting GROUP BY still excludes C

Capture3.PNG




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

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




View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
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.

PowerBI screen.jpg

 

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.

Thanks in advance

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

 

 

Capture.PNG




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

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




Murkow
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

In Query Editor, have you tried:

1. Filter Project Column to exclude Absense, then 

2. Highlight your User Column

3. Choose 'Group By' to SUM the hours

 

Does this help?

 

Raw Data:

Capture.PNG

 

Filter applied to exclude C:

Capture2.PNG

 

Resulting GROUP BY still excludes C

Capture3.PNG




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

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




Murkow
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

 

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!