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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Natasha93
Frequent Visitor

Measurement to calculate time in office per person

Hello Power BI Community 😊

 

I am putting together a report to show the percentage of time a person spends working in office compared to working from home.

My table includes the persons name, date and the status; In Office (IO), Working From Home (WFH), Out of Office (OOO).

The report can be filtered by date hierarchy and by the names of each person.

I plan to use the Card Visual to show the percentage of IO and WFH, the total that should not include dates spent OOO.

 

What measurment (other other method) would be most efficient for this?

 

Here's an example of the table:

 NameWeekdayDateStatus
JamesMonday18/10/2022OOO
JamesTuesday19/10/2022IO
JamesWednesday20/10/2022IO
JamesThursday21/10/2022IO
JamesFriday22/10/2022WFH
NatashaMonday18/10/2022WFH
NatashaTuesday19/10/2022WFH
NatashaWednesday20/10/2022IO
NatashaThursday21/10/2022IO
NatashaFriday22/10/2022IO
EvelynMonday18/10/2022OOO
EvelynTuesday19/10/2022IO
EvelynWednesday20/10/2022IO
EvelynThursday21/10/2022IO
EvelynFriday22/10/2022IO

 

Thanks for your time!

1 ACCEPTED SOLUTION
ReneMoawad
Resolver III
Resolver III

Hi @Natasha93,

 

You need to create a new measure with the below DAX

IO = 
Var allWorkingDays = CALCULATE(Count(TableName[Date]), Status <> "OOO")
Var inOffice = CALCULATE(Count(TableName[Date]), Status = "IO")
RETURN
DIVIDE(inOffice, allWorkingDays)

 

After you create this measure, change the type of it to Percentage

 

You can create the same formula for the WFH

View solution in original post

5 REPLIES 5
ReneMoawad
Resolver III
Resolver III

You need to add the below filter in BOLD

 

OOI_percent = 

VAR _IOcount = CALCULATE( COUNT( [name] ),FILTER( [YourTableName] , [Status] = "IO"))
VAR _Total = CALCULATE( COUNT( [name] ), Status <> "OOO")


RETURN
DIVIDE(_IOcount,_Total,0)

ReneMoawad
Resolver III
Resolver III

Hi @Natasha93,

 

You need to create a new measure with the below DAX

IO = 
Var allWorkingDays = CALCULATE(Count(TableName[Date]), Status <> "OOO")
Var inOffice = CALCULATE(Count(TableName[Date]), Status = "IO")
RETURN
DIVIDE(inOffice, allWorkingDays)

 

After you create this measure, change the type of it to Percentage

 

You can create the same formula for the WFH

thomthom_uk
Frequent Visitor

Hi there,

 

Assuming the date values represent whole working days, a simple count-based % measure should be sufficent.

 

Using IO as an example:

 

OOI_percent = 

VAR _IOcount = CALCULATE( COUNT( [name] ),FILTER( [YourTableName] , [Status] = "IO"))
VAR _Total = CALCULATE( COUNT( [name] ) )


RETURN
DIVIDE(_IOcount,_Total,0)

 

Do the same for WFH too and you should have your values. To note, everything in the [] will need adjusting to reflect the actual table and column names.

 

Hi @thomthom_uk ,

 

That worked! Thanks! 

2022-09-15_11-48-37.jpg

However, my only issue is that the In Office and Work From Home measurement also include Out of Office in the grand total.

If someone works in office on a Monday and Tuesday, but have booked Wed-Fri as holiday, techincally they've spent 100% of their working time at the office. 

Is there an adjustment that can be made to not include dates marked "OOO" in these measurements?

Glad it worked.

 

For exclusions, replace your total calculation with the following:

VAR _Total = CALCULATE( COUNT( [name] ),FILTER( [YourTableName] , [Status] <> "OOO"))

 

In general, adjust the filters to reflect what you need. You might need to add a note to report that the IO and WFH excludes OOO times, in case someone comments about the % values not adding up to 100%

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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