Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
The DAX for this works just fine, but the number seems very small for the 4 days of the month so far:
I believe this is due to is also calulating blank cells. I have tried to add the exclusion of blank cells, but Power BI doesn't like that:
This is what the worksheet I'm working on looks like:
Are there any changes I can make to make this work as expected?
Solved! Go to Solution.
Hi @Natasha93,
So this makes the problem a bit clear.
My suggestion in this case would nbe to get rid of the blanks in your data inside power query, unless you absolutely need the for some reason.
Next you can just use the following code to:
1. get the total number of times people have worked IO, which is 19
2. Get the total number of time people have worked IO or WFH, which is 65 (not 104, since we are not counting OOO). The double pipe symbol || stand for or condition
3. Divide the 2 numbers. (19/65 = 29.23%)
IO % =
var IO_Count = CALCULATE(COUNTROWS(Nata), FILTER(Nata, Nata[Status]="IO"))
var Total_Count = CALCULATE(COUNTROWS(Nata), FILTER(Nata, Nata[Status]="IO" || Nata[Status]="WFH"))
Return
DIVIDE(IO_Count,Total_Count)
Since in the total you have a filter for only WFH or IO, even if the blanks are present, it should not be a problem, as they will be filtered out.
I hope this solves your problem and if it does then please do mark it as the solution, so that others can reach the solution faster.
P.S. - I was working on some other solution and added your table to that solution, hence the table is named "Nata". Apologies!
Thank you,
Vishesh Jain
Proud to be a Super User!
Just want to add what I expect...
So for the 4 days of January so far, including Am and PM, there should only be 104 lines counted.
For In Office, the count is 19 making the total percentage 18%
For Out of office it's 39 or 38%
And Working from home is 46 or 44%.
It's definitley counting the blanks, because when included in my manual calculation In Office is 4%
Hi @Natasha93,
I am confused at to why do you have blanks in your Status?
If the blank values in your status stand for In Office, then my suggestion would be to replace the blanks with In Office in Power Query.
Also, please can you explain how the In Office count is 19 as I see only 2 IO on Thursday the 5th of Jan 2023.
The problem doesn't look all that complicated, just needs a little more explanation.
Could you please share some sample data, after masking the employee names, with something like employee IDs?
Thank you,
Vishesh Jain
Proud to be a Super User!
Hi @visheshjain
Sorry I realize I wasn't clear.
I want to only calculate In Office and Working From Home. The total of these two values would equal 100% of the employees working time.
"Out of Office" is included in the sheet as I do not want this to be included in the total working time.
So if Employee 1 works in office Monday and Tuesday, works from home Wednesday Thursday, but is out of office on Friday, than their time spent in office would be 50% of their working time.
It also appears that the blank cells under "Status" are being counted in the total percentage, which I also want to avoid, as my spreadsheet has dates entered in advance (actual file up until end of January)
Agent Name | Weekday | Date | AM/PM | Status |
1 | Monday | 02/01/2023 | AM | OOO |
1 | Monday | 02/01/2023 | PM | OOO |
2 | Monday | 02/01/2023 | AM | WFH |
2 | Monday | 02/01/2023 | PM | WFH |
3 | Monday | 02/01/2023 | AM | WFH |
3 | Monday | 02/01/2023 | PM | WFH |
4 | Monday | 02/01/2023 | AM | OOO |
4 | Monday | 02/01/2023 | PM | OOO |
5 | Monday | 02/01/2023 | AM | OOO |
5 | Monday | 02/01/2023 | PM | OOO |
6 | Monday | 02/01/2023 | AM | OOO |
6 | Monday | 02/01/2023 | PM | OOO |
7 | Monday | 02/01/2023 | AM | OOO |
7 | Monday | 02/01/2023 | PM | OOO |
8 | Monday | 02/01/2023 | AM | OOO |
8 | Monday | 02/01/2023 | PM | OOO |
9 | Monday | 02/01/2023 | AM | WFH |
9 | Monday | 02/01/2023 | PM | WFH |
10 | Monday | 02/01/2023 | AM | OOO |
10 | Monday | 02/01/2023 | PM | OOO |
11 | Monday | 02/01/2023 | AM | WFH |
11 | Monday | 02/01/2023 | PM | OOO |
12 | Monday | 02/01/2023 | AM | WFH |
12 | Monday | 02/01/2023 | PM | OOO |
13 | Monday | 02/01/2023 | AM | WFH |
13 | Monday | 02/01/2023 | PM | WFH |
1 | Tuesday | 03/01/2023 | AM | WFH |
1 | Tuesday | 03/01/2023 | PM | WFH |
2 | Tuesday | 03/01/2023 | AM | IO |
2 | Tuesday | 03/01/2023 | PM | IO |
3 | Tuesday | 03/01/2023 | AM | IO |
3 | Tuesday | 03/01/2023 | PM | IO |
4 | Tuesday | 03/01/2023 | AM | OOO |
4 | Tuesday | 03/01/2023 | PM | OOO |
5 | Tuesday | 03/01/2023 | AM | WFH |
5 | Tuesday | 03/01/2023 | PM | WFH |
6 | Tuesday | 03/01/2023 | AM | WFH |
6 | Tuesday | 03/01/2023 | PM | WFH |
7 | Tuesday | 03/01/2023 | AM | OOO |
7 | Tuesday | 03/01/2023 | PM | OOO |
8 | Tuesday | 03/01/2023 | AM | WFH |
8 | Tuesday | 03/01/2023 | PM | WFH |
9 | Tuesday | 03/01/2023 | AM | WFH |
9 | Tuesday | 03/01/2023 | PM | WFH |
10 | Tuesday | 03/01/2023 | AM | OOO |
10 | Tuesday | 03/01/2023 | PM | OOO |
11 | Tuesday | 03/01/2023 | AM | OOO |
11 | Tuesday | 03/01/2023 | PM | OOO |
12 | Tuesday | 03/01/2023 | AM | IO |
12 | Tuesday | 03/01/2023 | PM | IO |
13 | Tuesday | 03/01/2023 | AM | WFH |
13 | Tuesday | 03/01/2023 | PM | OOO |
1 | Wednesday | 04/01/2023 | AM | IO |
1 | Wednesday | 04/01/2023 | PM | IO |
2 | Wednesday | 04/01/2023 | AM | IO |
2 | Wednesday | 04/01/2023 | PM | IO |
3 | Wednesday | 04/01/2023 | AM | WFH |
3 | Wednesday | 04/01/2023 | PM | WFH |
4 | Wednesday | 04/01/2023 | AM | WFH |
4 | Wednesday | 04/01/2023 | PM | WFH |
5 | Wednesday | 04/01/2023 | AM | WFH |
5 | Wednesday | 04/01/2023 | PM | WFH |
6 | Wednesday | 04/01/2023 | AM | WFH |
6 | Wednesday | 04/01/2023 | PM | WFH |
7 | Wednesday | 04/01/2023 | AM | IO |
7 | Wednesday | 04/01/2023 | PM | IO |
8 | Wednesday | 04/01/2023 | AM | WFH |
8 | Wednesday | 04/01/2023 | PM | WFH |
9 | Wednesday | 04/01/2023 | AM | WFH |
9 | Wednesday | 04/01/2023 | PM | WFH |
10 | Wednesday | 04/01/2023 | AM | OOO |
10 | Wednesday | 04/01/2023 | PM | OOO |
11 | Wednesday | 04/01/2023 | AM | OOO |
11 | Wednesday | 04/01/2023 | PM | OOO |
12 | Wednesday | 04/01/2023 | AM | IO |
12 | Wednesday | 04/01/2023 | PM | IO |
13 | Wednesday | 04/01/2023 | AM | OOO |
13 | Wednesday | 04/01/2023 | PM | OOO |
1 | Thursday | 05/01/2023 | AM | WFH |
1 | Thursday | 05/01/2023 | PM | WFH |
2 | Thursday | 05/01/2023 | AM | IO |
2 | Thursday | 05/01/2023 | PM | IO |
3 | Thursday | 05/01/2023 | AM | IO |
3 | Thursday | 05/01/2023 | PM | WFH |
4 | Thursday | 05/01/2023 | AM | WFH |
4 | Thursday | 05/01/2023 | PM | WFH |
5 | Thursday | 05/01/2023 | AM | WFH |
5 | Thursday | 05/01/2023 | PM | WFH |
6 | Thursday | 05/01/2023 | AM | WFH |
6 | Thursday | 05/01/2023 | PM | WFH |
7 | Thursday | 05/01/2023 | AM | OOO |
7 | Thursday | 05/01/2023 | PM | OOO |
8 | Thursday | 05/01/2023 | AM | WFH |
8 | Thursday | 05/01/2023 | PM | WFH |
9 | Thursday | 05/01/2023 | AM | WFH |
9 | Thursday | 05/01/2023 | PM | WFH |
10 | Thursday | 05/01/2023 | AM | OOO |
10 | Thursday | 05/01/2023 | PM | OOO |
11 | Thursday | 05/01/2023 | AM | OOO |
11 | Thursday | 05/01/2023 | PM | OOO |
12 | Thursday | 05/01/2023 | AM | IO |
12 | Thursday | 05/01/2023 | PM | IO |
13 | Thursday | 05/01/2023 | AM | OOO |
13 | Thursday | 05/01/2023 | PM | OOO |
1 | Friday | 06/01/2023 | AM | |
1 | Friday | 06/01/2023 | PM | |
2 | Friday | 06/01/2023 | AM | |
2 | Friday | 06/01/2023 | PM | |
3 | Friday | 06/01/2023 | AM | |
3 | Friday | 06/01/2023 | PM | |
4 | Friday | 06/01/2023 | AM | |
4 | Friday | 06/01/2023 | PM | |
5 | Friday | 06/01/2023 | AM | |
5 | Friday | 06/01/2023 | PM | |
6 | Friday | 06/01/2023 | AM | |
6 | Friday | 06/01/2023 | PM | |
7 | Friday | 06/01/2023 | AM | |
7 | Friday | 06/01/2023 | PM | |
8 | Friday | 06/01/2023 | AM | |
8 | Friday | 06/01/2023 | PM | |
9 | Friday | 06/01/2023 | AM | |
9 | Friday | 06/01/2023 | PM | |
10 | Friday | 06/01/2023 | AM | |
10 | Friday | 06/01/2023 | PM | |
11 | Friday | 06/01/2023 | AM | |
11 | Friday | 06/01/2023 | PM | |
12 | Friday | 06/01/2023 | AM | |
12 | Friday | 06/01/2023 | PM | |
13 | Friday | 06/01/2023 | AM | |
13 | Friday | 06/01/2023 | PM |
Hi @Natasha93,
So this makes the problem a bit clear.
My suggestion in this case would nbe to get rid of the blanks in your data inside power query, unless you absolutely need the for some reason.
Next you can just use the following code to:
1. get the total number of times people have worked IO, which is 19
2. Get the total number of time people have worked IO or WFH, which is 65 (not 104, since we are not counting OOO). The double pipe symbol || stand for or condition
3. Divide the 2 numbers. (19/65 = 29.23%)
IO % =
var IO_Count = CALCULATE(COUNTROWS(Nata), FILTER(Nata, Nata[Status]="IO"))
var Total_Count = CALCULATE(COUNTROWS(Nata), FILTER(Nata, Nata[Status]="IO" || Nata[Status]="WFH"))
Return
DIVIDE(IO_Count,Total_Count)
Since in the total you have a filter for only WFH or IO, even if the blanks are present, it should not be a problem, as they will be filtered out.
I hope this solves your problem and if it does then please do mark it as the solution, so that others can reach the solution faster.
P.S. - I was working on some other solution and added your table to that solution, hence the table is named "Nata". Apologies!
Thank you,
Vishesh Jain
Proud to be a Super User!
User | Count |
---|---|
115 | |
95 | |
87 | |
76 | |
65 |
User | Count |
---|---|
138 | |
113 | |
110 | |
98 | |
93 |