Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I'm looking to create a measure that counts the number of days a worker has worked in a period. This would be easy enough but the data is set up so that if a worker does enough hours to get overtime, there will be a new line for each over time rate as below:
Worker | Date | Rate | Hours |
Tom | 23/06/2022 | 1.5x | 1 |
Tom | 23/06/2022 | 1x | 4 |
John | 23/06/2022 | 1x | 8 |
John | 23/06/2022 | 1.5x | 2 |
John | 21/06/2022 | 1x | 6 |
John | 22/06/2022 | 1x | 7 |
In this example Tom would have worked 1 day and John 3 days.
The end goal is working out the average number of days worked over a period, so in this example the average days worked would be 2 days. We could then track this over the entire workforce in a line graph for example.
Is this possible in DAX?
@Anonymous , Try like
averagex(Values(Table[Worker]), calculate( Countrows(Summarize(Table, Table[Worker], Table[Date]))))
User | Count |
---|---|
106 | |
86 | |
81 | |
73 | |
72 |
User | Count |
---|---|
112 | |
100 | |
98 | |
72 | |
66 |