Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Proud to give back to the community!
Thank You!
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
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
Proud to give back to the community!
Thank You!
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:
Filter applied to exclude C:
Resulting GROUP BY still excludes C
Proud to give back to the community!
Thank You!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |