Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

Hi @v-huizhn-msft

 

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

Capture7.PNG

 

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)

Capture8.PNGCapture9.PNG

 

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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 ?!?

 

Capture6.PNG

Hi @Anonymous,

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

Anonymous
Not applicable

Hi @v-huizhn-msft

 

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

Capture7.PNG

 

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)

Capture8.PNGCapture9.PNG

 

 

Hi @Anonymous,

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.