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".  ...
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:

     

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:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      PaulDBrown You're a legend! Muchos Gracias.