cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Matt_Evans Frequent Visitor
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
Matt_Evans Frequent Visitor
Frequent Visitor

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

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 Smiley Happy

 

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

 

 

4 REPLIES 4
Matt_Evans Frequent Visitor
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 ?!?

 

Capture6.PNG

v-huizhn-msft Super Contributor
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

Matt_Evans Frequent Visitor
Frequent Visitor

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

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 Smiley Happy

 

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

 

 

v-huizhn-msft Super Contributor
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

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 68 members 971 guests
Please welcome our newest community members: