Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am trying to calculate work efficiency based on count of workers from slicer selection. Here's the scenario.
There are 3 tables in my data model:
What I need is to visualize work efficiency for the team as well for individuals. It should count time in minutes compared to total available time (8 hours = 480 minutes per working day).
Therefore I need to implement following condition:
I have managed to create following measures which are working almost perfectly. Problem is that when I want to add field "Type of task" into legend, calculation is automatically counting only those workers that did such type of task. Or another case, if there is only 1 worker working in particular day, measure is taking into cosideration only 1 worker instead of counting the total number of workers (assuming no selection on slicer "Name" is made). Work efficiency should always take the total time of whole team unless the selection on slicer "Name" is made. I have tried to add ALL, ALLEXCEPT but all my attemps ended in errors.
Nr. of team members =
VAR _allmembers = COUNTROWS(
FILTER(DIM_Users,
DIM_Users[Status] in {"active"}
)
VAR _selectedmembers = COUNTROWS(VALUES('pbi_FACT_TasksWorkingTime per person)'[Name]))
RETURN
IF(ISFILTERED('pbi_FACT_TasksWorkingTime per person'[Name]), _selectedmembers, _allmembers)
Work efficiency % =
VAR _first = CALCULATE ( MIN ( 'Date table'[Date] ), ALLSELECTED ( 'Date table'[Date] ) )
VAR _last = CALCULATE ( MAX ( 'Date table'[Date] ), ALLSELECTED ( 'Date table'[Date] ) )
VAR _nrofdays = NETWORKDAYS(_first, _last, 1)
RETURN
DIVIDE(
SUM('pbi_FACT_TasksWorkingTime per person)'[Time in minutes]),
(_nrofdays *480) * [Nr. of team members],
0
)
If needed, you can use following table for testing - let's assume we have 3 workers in team and 2 types of tasks (Type 1 & Type 2) - in reality I have 20+ different types.
On 17.1.2023, result should look like below:
1. When no selection on slicer "Name" then Work efficiency: (400+300+450) / (480 * 3) = 79,85% /* 480 is total available amount of minutes per worker */
2. When selecting Worker 1 and Worker 2 then: (400 + 300) / (480 * 2) = 72.92%
3. When selecting Worker 2 only then: (300) / (480 * 1) = 62.5%
On 10.1.2023, when adding Type of task as legend into column graph and making no selection on slicer "Name", then Work efficiency should be 400 / (480 * 3) = 27.78% /* instead of my current result 400 / (480 * 1) = 83.33% which is not correct */
Task date | Name | Time in minutes | Type of task |
10.1.2023 | Worker 1 | 400 | Type 1 |
15.1.2023 | Worker 1 | 350 | Type 1 |
16.1.2023 | Worker 2 | 200 | Type 2 |
17.1.2023 | Worker 1 | 400 | Type 1 |
17.1.2023 | Worker 2 | 300 | Type 1 |
17.1.2023 | Worker 3 | 450 | Type 2 |
18.1.2023 | Worker 3 | 330 | Type 1 |
19.1.2023 | Worker 1 | 280 | Type 1 |
19.1.2023 | Worker 2 | 440 | Type 2 |
Hope that description is clear but if you need more details, just let me know.
Thank you for any help.
IvanS
Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi @lbendlin ,
sample table is enclosed in the last section in my post together with expected result. Please take a look if it is sufficient or not.
There are 3 tables in my data model:
DIM_Date Table (calculated table)
FACT_TasksWorkingTime per person (calculated table)
DIM_Users
please provide sample data that completely covers your question.
I have created sample report available on this link: https://www.dropbox.com/s/3r5vtdwejykunt5/Sample%20report.pbix?dl=0
Let me know if you can download the file.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |