Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IvanS
Helper V
Helper V

Calculation of work efficiency based on count of workers from slicer selection

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:

  • DIM_Date Table (calculated table)
  • FACT_TasksWorkingTime per person (calculated table)
  • DIM_Users

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:

  1. If slicer Name from either FACT_TasksWorkingTime per person or DIM_Users has no selection, then calculate the total number of active workers from DIM_Users table (column Status = "active")
  2. Otherwise, use the count of workers from slicer Name

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 dateNameTime in minutesType of task
10.1.2023Worker 1400Type 1
15.1.2023Worker 1350Type 1
16.1.2023Worker 2200Type 2
17.1.2023Worker 1400Type 1
17.1.2023Worker 2300Type 1
17.1.2023Worker 3450Type 2
18.1.2023Worker 3330Type 1
19.1.2023Worker 1280Type 1
19.1.2023Worker 2440Type 2

 

Hope that description is clear but if you need more details, just let me know.

 

Thank you for any help.

IvanS

5 REPLIES 5
lbendlin
Super User
Super User

When you filter on a fact table that filter will not travel up to the dimension table. You can use that to your advantage if you like, but the general rule is "Don't filter inside the fact table".

 

lbendlin_0-1676929264171.png

 

 

See attached.

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.