Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Measure Overtime Hours, two categories

Hi all,

 

See my sample file here.

 

I'm trying to calculate the "Normal Hours", Overtime Category 1 Hours"  and "Overtime Category 2 Hours" out of my fact table which contains "Hours per day". 

Normal Hours are working hours <= 8;

Overtime Category 1 are working hours >8 and <=10;

Overtime Category 2 are working hours >10.

 

As you can see in my sample file I already got the right results in the columns. Nevertheless, the Total of "Category 1 Overtime" isn't what I had expected. It used to be +14 instead of -14.

Anyone knows what I'm doing wrong?

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    I see... Ok, try:

    Overtime Hours = [Total Hours] - 8
    Cat 1 Overtime =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( Urenregistratie, [Overtime Hours] > 0 ),
            Kalender[Date],
            Personeel[Uitzendkracht]
        ),
        "Cat 1", IF ( [Overtime Hours] > 2, 2, [Overtime Hours] )
    ),
    [Cat 1]
)

    Cat 2 Overtime =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( Urenregistratie, [Overtime Hours] > 2 ),
            Kalender[Date],
            Personeel[Uitzendkracht]
        ),
        "Cat 2", [Overtime Hours] - [Cat 1 Overtime]
    ),
    [Cat 2]
)

     

    To get:

     

4 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Try:

     

    Overtime Hours = [Total Hours] - 8

     

     

    And then:

     

    Cat 1 Overtime =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( Urenregistratie, [Overtime Hours] > 2 ),
            Kalender[Date],
            Personeel[Uitzendkracht]
        ),
        "Cat 1", [Overtime Hours]
    ),
    [Cat 1]
)

    Cat 2 Overtime =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( Urenregistratie, [Overtime Hours] >= 1 && [Overtime Hours] < 3 ),
            Kalender[Date],
            Personeel[Uitzendkracht]
        ),
        "Cat 2", [Overtime Hours]
    ),
    [Cat 2]
)

    and you will get this:

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi PaulDBrown 

     

    Thank you for your reply.

    Your answer is pretty close to my desired result. Nevertheless, Total Hours can be part of both Category 1 and Category 2 Overtime. 

     

    For example,

    11 working hours needs to be splitted by:

    8 normal hours;

    2 Category 1 overtime hours;

    1 Category 2 overtime hours.

     

    And not by 8 normal hours and 3 hours Category 2 as in your answer.

     

    This is relevant for the total wage of the employee.
    He gets paid as following:

    0 - 8 hours 100%

    >8 - <= 10 hours 120% (so, only for the 2 hours between 8 and 10)
    > 10 hours 130% (so, only for the hours higher than 10)

    • PaulDBrown's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      5 years ago

      I see... Ok, try:

      Overtime Hours = [Total Hours] - 8
      Cat 1 Overtime =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( Urenregistratie, [Overtime Hours] > 0 ),
            Kalender[Date],
            Personeel[Uitzendkracht]
        ),
        "Cat 1", IF ( [Overtime Hours] > 2, 2, [Overtime Hours] )
    ),
    [Cat 1]
)

      Cat 2 Overtime =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( Urenregistratie, [Overtime Hours] > 2 ),
            Kalender[Date],
            Personeel[Uitzendkracht]
        ),
        "Cat 2", [Overtime Hours] - [Cat 1 Overtime]
    ),
    [Cat 2]
)

       

      To get: