Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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%
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
101 | |
88 | |
35 | |
35 |
User | Count |
---|---|
152 | |
100 | |
83 | |
63 | |
54 |