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.
Hello,
I'm using Power BI to manage Timesheet reports.
I need to create a custom column which counts the number of entries per day for a given client. Once I have that number, I need to substract a total of 45min per day spread over all entries of that client.
Here is some sample data:
date | client | hours |
06.10.2019 | A | 1 |
06.10.2019 | A | 2 |
06.10.2019 | B | 1.5 |
06.10.2019 | A | 3 |
06.10.2019 | C | 0.5 |
07.10.2019 | A | 4 |
07.10.2019 | A | 1 |
07.10.2019 | C | 3 |
08.10.2019 | A | 3 |
08.10.2019 | D | 1 |
08.10.2019 | A | 2 |
08.10.2019 | B | 0.5 |
08.10.2019 | A | 1.5 |
What I'm looking to get is this (using Client A for the count) :
date | client | hours | count_per_day | hours v2 | |
06.10.2019 | A | 1 | 3 | 0.75 | |
06.10.2019 | A | 2 | 3 | 1.75 | |
06.10.2019 | B | 1.5 | 3 | 1.5 | |
06.10.2019 | A | 3 | 3 | 2.75 | |
06.10.2019 | C | 0.5 | 3 | 0.5 | |
07.10.2019 | A | 4 | 2 | 3.625 | |
07.10.2019 | A | 1 | 2 | 0.625 | |
07.10.2019 | C | 3 | 2 | 3 | |
08.10.2019 | A | 3 | 3 | 2.75 | |
08.10.2019 | D | 1 | 3 | 1 | |
08.10.2019 | A | 2 | 3 | 1.75 | |
08.10.2019 | B | 0.5 | 3 | 0.5 | |
08.10.2019 | A | 1.5 | 3 | 1.25 |
The hours v2 is simple to get once I get the Count/day :
IF(Client = "A" THEN hoursv2 = hours-0.75/count_per_day ELSE hours)
How can I achieve this count as a new column ?
Thanks !
Solved! Go to Solution.
@Anonymous
As a calc column you can use
Column = CALCULATE ( COUNTROWS ( TableName ), ALLEXCEPT ( TableName, TableName[Dates] ), TableName[client] = "A" )
@Anonymous
As a custom Column in Power Query
=Table.RowCount( Table.SelectRows( #"Changed Type", (x)=> x[date]=[date] and x[client]="A" ) )
@Anonymous
As a calc column you can use
Column = CALCULATE ( COUNTROWS ( TableName ), ALLEXCEPT ( TableName, TableName[Dates] ), TableName[client] = "A" )
@Anonymous
As a custom Column in Power Query
=Table.RowCount( Table.SelectRows( #"Changed Type", (x)=> x[date]=[date] and x[client]="A" ) )
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 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |