Forum Discussion

Vitaliy's avatar
Vitaliy
Helper I
7 years ago
Solved

Calculate Overtime

Hi all!

 

I have a data model ( you can download pbix file here)

 

i want to build a report with table like this one below:

 

But my Overtime field and Overtime card KPI in total values is not fully correct, they are Blank.
How I calculate:

LogHours_forPeriod = 
CALCULATE(
    [LogHours];
    USERELATIONSHIP(worklogs[date_only];DateTime_Vacations[Date])
)
PlannedHours_forMonth = SUMx(VALUES(worklogs[username]);CALCULATE(sum(planned_hours[Working hours])))
Overtime help measure = worklogs[LogHours_forPeriod]-planned_hours[PlannedHours_forMonth]

 

And Overtime:

Overtime = SUMX(FILTER(SUMMARIZE('worklogs';worklogs[username];"ABCD";[Overtime help measure]);[Overtime help measure]>0);[Overtime help measure])

but I want to show in Total row of the Overtime field value = 32 (sum of positive values for every month and user), now this cell is blank (Overtime KPI card Blank too)
What's wrong in my "Overtime" measure?
Please, help!

 

Thanks.

  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Vitaliy ,

    After play with your sample, I found your overtime measure contians filter on 'overtime help' measure if its result less than zero. 

    Overtime = SUMX(FILTER(SUMMARIZE('worklogs',worklogs[username],"ABCD",[Overtime help measure]),[Overtime help measure]>0),[Overtime help measure])

    Regards,

    Xiaoxin Sheng

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Vitaliy ,

    I can't view your sample file, it seems like you haven't shared it correctly.

    Regards,

    Xiaoxin Sheng

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi Vitaliy ,

        After play with your sample, I found your overtime measure contians filter on 'overtime help' measure if its result less than zero. 

        Overtime = SUMX(FILTER(SUMMARIZE('worklogs',worklogs[username],"ABCD",[Overtime help measure]),[Overtime help measure]>0),[Overtime help measure])

        Regards,

        Xiaoxin Sheng