Measurement to calculate time in office per person

[Natasha93]

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:

Name	Weekday	Date	Status
James	Monday	18/10/2022	OOO
James	Tuesday	19/10/2022	IO
James	Wednesday	20/10/2022	IO
James	Thursday	21/10/2022	IO
James	Friday	22/10/2022	WFH
Natasha	Monday	18/10/2022	WFH
Natasha	Tuesday	19/10/2022	WFH
Natasha	Wednesday	20/10/2022	IO
Natasha	Thursday	21/10/2022	IO
Natasha	Friday	22/10/2022	IO
Evelyn	Monday	18/10/2022	OOO
Evelyn	Tuesday	19/10/2022	IO
Evelyn	Wednesday	20/10/2022	IO
Evelyn	Thursday	21/10/2022	IO
Evelyn	Friday	22/10/2022	IO

Thanks for your time!

[ReneMoawad]

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 · Accepted solution]

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]

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.

[Natasha93]

Hi @thomthom_uk ,

That worked! Thanks! 

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?

[thomthom_uk]

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%