cancel
Showing results for
Did you mean:
Frequent Visitor

## Measure calculated at daily Context/scope then summed up at weekly/monthly level

We are trying to produce a worker missing timesheet daily measure but i'm struggling with how to scope it so the measure is only calculated at a per worker per day level and then summed up any higher than that.

The scenario is we have is employees record time throughout the day in unit chunks.  They have a a daily target of a number of units they should records. We want to identify days where they didn't record sufficient time. So we need to sum up the number of units they record each day see if it matches or exceeds the target for the day, if it exceeds then target met = 1 else 0.

We then want to see how many missed daily targets were incurred and be able to view hit/missed targets count at a day/week/month level.

My current DAX expression only works at a day level, i can't work out how to get it to calculated at a workerperday level

Daily Timesheet Target Met = SUMX(VALUES(TargetDate[Date]), IF([Target Timesheet Hours] <> 0 && [Total Timesheet Hours] > [Target Timesheet Hours],1,0))

Any help much appreciated

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Measure calculated at daily Context/scope then summed up at weekly/monthly level

You are correct, they are my measures (I've posted the forulas below)

I've got this working now but i'm not sure its the most elegant solution but i couldn't get me head round how to nest everything in a single DAX expression, if you are able to offer any help it would be much appreciated

Heres a snapshot of my model

So what i've done is

1) Define a calculated table (DailyHrsRecordedTotal) where the daily transactions are summed up by worker by day

DailyHrsRecordedTotal = SUMMARIZECOLUMNS(
'TargetDate'[DateKey],
'Worker'[Worker_Key],
'FACT_MatterPostings',
"DailyHrs-Recorded",
[Total Timesheet Hours])

Total Timesheet Hours = SUM(FACT_MatterPostings[HoursRecorded])

2) Define relationships in the model between this table and the Worker and TargetDate dimensions

3) Create another calculated table (DailyTimesheet) where these daily figures are calulated into a Daily Target Met/Missed field

DailyTimesheet = SUMMARIZECOLUMNS(
TargetDate[DateKey],
'Worker'[Worker_Key],
'Fact WorkerTargets',
"DailyTarget",
[Target Timesheet Hours],
"DailyRecorded",
SUM(DailyHrsRecordedTotal[DailyHrsTotal]),
"DailyTargetMet",
IF([Target Timesheet Hours] <> 0 && SUM(DailyHrsRecordedTotal[DailyHrsTotal]) >= [Target Timesheet Hours],1,0),
"DailyTargetMissed",
IF([Target Timesheet Hours] <> 0 && SUM(DailyHrsRecordedTotal[DailyHrsTotal]) < [Target Timesheet Hours],1,0)
)

Target Timesheet Hours = SUM('Fact WorkerTargets'[Target Timesheet Hours])

Then if i drop these columns onto my report table i get the correct figures (and this aggregates up correctly at a daily level)

4 REPLIES 4
Frequent Visitor

## Re: Measure calculated at daily Context/scope then summed up at weekly/monthly level

I had a bit more of a play and using the SUMMARIZE function i think i am now calculating at a grain of worker per day, however i'm getting some weird results which suggests my context is still not right.  You can see below that  a worker

has recorded 8.6 hours and their target was 7, yet my calulation is showing as having not met daily target, yet for the other worker they recorded 7.4 and their target was also 7 and that is showing as having met target ?!?

Super Contributor

## Re: Measure calculated at daily Context/scope then summed up at weekly/monthly level

Hi @Matt_Evans,

The [Target Timesheet Hours] and [Target Timesheet Hours] are measure in your scenario, right? If it is, could you please share the detailed formula and sample table(you can create dummy data if your data is confidential) for further analysis?

Best Regards,
Angelia

Frequent Visitor

## Re: Measure calculated at daily Context/scope then summed up at weekly/monthly level

You are correct, they are my measures (I've posted the forulas below)

I've got this working now but i'm not sure its the most elegant solution but i couldn't get me head round how to nest everything in a single DAX expression, if you are able to offer any help it would be much appreciated

Heres a snapshot of my model

So what i've done is

1) Define a calculated table (DailyHrsRecordedTotal) where the daily transactions are summed up by worker by day

DailyHrsRecordedTotal = SUMMARIZECOLUMNS(
'TargetDate'[DateKey],
'Worker'[Worker_Key],
'FACT_MatterPostings',
"DailyHrs-Recorded",
[Total Timesheet Hours])

Total Timesheet Hours = SUM(FACT_MatterPostings[HoursRecorded])

2) Define relationships in the model between this table and the Worker and TargetDate dimensions

3) Create another calculated table (DailyTimesheet) where these daily figures are calulated into a Daily Target Met/Missed field

DailyTimesheet = SUMMARIZECOLUMNS(
TargetDate[DateKey],
'Worker'[Worker_Key],
'Fact WorkerTargets',
"DailyTarget",
[Target Timesheet Hours],
"DailyRecorded",
SUM(DailyHrsRecordedTotal[DailyHrsTotal]),
"DailyTargetMet",
IF([Target Timesheet Hours] <> 0 && SUM(DailyHrsRecordedTotal[DailyHrsTotal]) >= [Target Timesheet Hours],1,0),
"DailyTargetMissed",
IF([Target Timesheet Hours] <> 0 && SUM(DailyHrsRecordedTotal[DailyHrsTotal]) < [Target Timesheet Hours],1,0)
)

Target Timesheet Hours = SUM('Fact WorkerTargets'[Target Timesheet Hours])

Then if i drop these columns onto my report table i get the correct figures (and this aggregates up correctly at a daily level)

Super Contributor

## Re: Measure calculated at daily Context/scope then summed up at weekly/monthly level

Hi @Matt_Evans,

Congratulations, you got right result based on your solution. Please mark your reply as answer, so more members will benefit from here. After check, I don't find any other elegant way to do it. I can post update if I have new idea.

Thanks,
Angelia

Announcements

Power BI Super User, Greg Deckler, explains

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 68 members 971 guests
Recent signins: