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:

     

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.