Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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!
Solved! Go to 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
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)
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
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!
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%
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
80 | |
69 | |
60 |