Forum Discussion

dsj8wksnnckk's avatar
dsj8wksnnckk
Resolver I
2 years ago
Solved

Count resources on bench

Hi, I need help with counting people on bench. I have the following tables: Table1 [Booking] with columns: Resource Name, Project Type, Status Name, Start Date, Duration Table2 [Resourc...
Need Help
  • dsj8wksnnckk's avatar
    dsj8wksnnckk
    2 years ago

     


    The Billing Type is a Calculated Column using data on Project and additional dimensional table containing more detailed information on Project.

    As you can see, there are multiple instances of Duration in 1 day.

    Definition of bench: a Resource is on bench if it has NO duration for Status Name = Hard && Project Type = Commercial && Billing Type = Chargeable

  • dsj8wksnnckk's avatar
    dsj8wksnnckk
    2 years ago

    I resolved this:

    CountOnBench =
    SUMX(
        VALUES('Resources[Resource Name]'),
        [IsOnBench]
    )

    IsOnBench =
    IF(
        [Allocation] = 0,
        1, BLANK()
    )


Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,dsj8wksnnckk ,I am glad to help you.

According to your description, you want to filter records related to projects with a null value for Duration based on the status of the Duration of the project.

This includes tech stack, seniority, resources and corresponding bench counts.

I ran the following test and presented the results as a table of calculations (I also created the corresponding measure)

The results can be seen by placing the table of calculations in a matrix

Here is my test data

Based on your description, you want to filter the records related to the project's Duration with a null value based on the status of the Duration.

This includes tech stacks, seniority, resources and corresponding bench counts.

I ran the following test and presented the results as a table of calculations (I also created the corresponding measure)

The results can be seen by placing the calculate table in a matrix

Here is my test data
Booking :


The code of calculate column C_dur:

C_dur = IF(ISBLANK('Booking'[Duration]),0,1)

Resources:

Calculate table created:

The final result is as follows.

Could you give me some specific data that is not private, that would be very helpful in solving your problem.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

dsj8wksnnckk's avatar
dsj8wksnnckk
Resolver I
2 years ago

Hi Anonymous,

thanks for your assistance. I am uploading additional screenshots - did in excel:

This last one is mimicking the matrix visual with drill down. 
Additionally, filtering by project type is also needed, I didn't do an example of that. 

In the Booking there is no null field for Duration, it has to be compared to the existing Resources with Active status (as someone might be an active resource but had no booking - therefore is on bench)
Also, soft bookings, and some project types don't count (non commercial projects, internal, etc) so they also must be excluded.