Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
fhill
Resident Rockstar
Resident Rockstar

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

fhill
Resident Rockstar
Resident Rockstar

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

fhill
Resident Rockstar
Resident Rockstar

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 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.