Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Natasha93
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 DAXCurrent 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:

ErrorError

 

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

WorksheetWorksheet

 

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

 

1 ACCEPTED 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%)

 

visheshjain_0-1672921720192.png

 

 

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

Did I answer your question?
If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!



View solution in original post

4 REPLIES 4
Natasha93
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%

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

Did I answer your question?
If yes, then please mark my post as a solution!

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 NameWeekdayDateAM/PMStatus
1Monday02/01/2023AMOOO
1Monday02/01/2023PMOOO
2Monday02/01/2023AMWFH
2Monday02/01/2023PMWFH
3Monday02/01/2023AMWFH
3Monday02/01/2023PMWFH
4Monday02/01/2023AMOOO
4Monday02/01/2023PMOOO
5Monday02/01/2023AMOOO
5Monday02/01/2023PMOOO
6Monday02/01/2023AMOOO
6Monday02/01/2023PMOOO
7Monday02/01/2023AMOOO
7Monday02/01/2023PMOOO
8Monday02/01/2023AMOOO
8Monday02/01/2023PMOOO
9Monday02/01/2023AMWFH
9Monday02/01/2023PMWFH
10Monday02/01/2023AMOOO
10Monday02/01/2023PMOOO
11Monday02/01/2023AMWFH
11Monday02/01/2023PMOOO
12Monday02/01/2023AMWFH
12Monday02/01/2023PMOOO
13Monday02/01/2023AMWFH
13Monday02/01/2023PMWFH
1Tuesday03/01/2023AMWFH
1Tuesday03/01/2023PMWFH
2Tuesday03/01/2023AMIO
2Tuesday03/01/2023PMIO
3Tuesday03/01/2023AMIO
3Tuesday03/01/2023PMIO
4Tuesday03/01/2023AMOOO
4Tuesday03/01/2023PMOOO
5Tuesday03/01/2023AMWFH
5Tuesday03/01/2023PMWFH
6Tuesday03/01/2023AMWFH
6Tuesday03/01/2023PMWFH
7Tuesday03/01/2023AMOOO
7Tuesday03/01/2023PMOOO
8Tuesday03/01/2023AMWFH
8Tuesday03/01/2023PMWFH
9Tuesday03/01/2023AMWFH
9Tuesday03/01/2023PMWFH
10Tuesday03/01/2023AMOOO
10Tuesday03/01/2023PMOOO
11Tuesday03/01/2023AMOOO
11Tuesday03/01/2023PMOOO
12Tuesday03/01/2023AMIO
12Tuesday03/01/2023PMIO
13Tuesday03/01/2023AMWFH
13Tuesday03/01/2023PMOOO
1Wednesday04/01/2023AMIO
1Wednesday04/01/2023PMIO
2Wednesday04/01/2023AMIO
2Wednesday04/01/2023PMIO
3Wednesday04/01/2023AMWFH
3Wednesday04/01/2023PMWFH
4Wednesday04/01/2023AMWFH
4Wednesday04/01/2023PMWFH
5Wednesday04/01/2023AMWFH
5Wednesday04/01/2023PMWFH
6Wednesday04/01/2023AMWFH
6Wednesday04/01/2023PMWFH
7Wednesday04/01/2023AMIO
7Wednesday04/01/2023PMIO
8Wednesday04/01/2023AMWFH
8Wednesday04/01/2023PMWFH
9Wednesday04/01/2023AMWFH
9Wednesday04/01/2023PMWFH
10Wednesday04/01/2023AMOOO
10Wednesday04/01/2023PMOOO
11Wednesday04/01/2023AMOOO
11Wednesday04/01/2023PMOOO
12Wednesday04/01/2023AMIO
12Wednesday04/01/2023PMIO
13Wednesday04/01/2023AMOOO
13Wednesday04/01/2023PMOOO
1Thursday05/01/2023AMWFH
1Thursday05/01/2023PMWFH
2Thursday05/01/2023AMIO
2Thursday05/01/2023PMIO
3Thursday05/01/2023AMIO
3Thursday05/01/2023PMWFH
4Thursday05/01/2023AMWFH
4Thursday05/01/2023PMWFH
5Thursday05/01/2023AMWFH
5Thursday05/01/2023PMWFH
6Thursday05/01/2023AMWFH
6Thursday05/01/2023PMWFH
7Thursday05/01/2023AMOOO
7Thursday05/01/2023PMOOO
8Thursday05/01/2023AMWFH
8Thursday05/01/2023PMWFH
9Thursday05/01/2023AMWFH
9Thursday05/01/2023PMWFH
10Thursday05/01/2023AMOOO
10Thursday05/01/2023PMOOO
11Thursday05/01/2023AMOOO
11Thursday05/01/2023PMOOO
12Thursday05/01/2023AMIO
12Thursday05/01/2023PMIO
13Thursday05/01/2023AMOOO
13Thursday05/01/2023PMOOO
1Friday06/01/2023AM 
1Friday06/01/2023PM 
2Friday06/01/2023AM 
2Friday06/01/2023PM 
3Friday06/01/2023AM 
3Friday06/01/2023PM 
4Friday06/01/2023AM 
4Friday06/01/2023PM 
5Friday06/01/2023AM 
5Friday06/01/2023PM 
6Friday06/01/2023AM 
6Friday06/01/2023PM 
7Friday06/01/2023AM 
7Friday06/01/2023PM 
8Friday06/01/2023AM 
8Friday06/01/2023PM 
9Friday06/01/2023AM 
9Friday06/01/2023PM 
10Friday06/01/2023AM 
10Friday06/01/2023PM 
11Friday06/01/2023AM 
11Friday06/01/2023PM 
12Friday06/01/2023AM 
12Friday06/01/2023PM 
13Friday06/01/2023AM 
13Friday06/01/2023PM 

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%)

 

visheshjain_0-1672921720192.png

 

 

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

Did I answer your question?
If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!



Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.