cancel
Showing results 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

Frequent Visitor

## DAX for Percentage Based on Three Status'

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:

Current DAX

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:

Error

This is what the worksheet I'm working on looks like:

Worksheet

Are there any changes I can make to make this work as expected?

1 ACCEPTED SOLUTION
Solution Supplier

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

If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!

4 REPLIES 4
Frequent Visitor

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%

Solution Supplier

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

If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!

Frequent Visitor

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
Solution Supplier

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

If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!