SUM of distinct rows using more filters

[Murkow]

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

[Anonymous]

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

[Murkow]

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]

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

[Murkow]

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

Filter applied to exclude C:

Resulting GROUP BY still excludes C

[Murkow]

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