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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Natasha93
Frequent Visitor

DAX for Average Calculation

Hello all,

 

I have a dashboard to measure the % of people working in office compared to working from home.

But for the life of me, I am unable to make a DAX that shows the average % of time spent in the office. Is anyone able to show me how this could be done?

 

Here's what I'm using at the moment:

 

Table: "Register"

Agent Name

WeekdayDate DD/MMMMAM/PMStatus
JohnMonday03 OctoberAMIO
JohnMonday03 OctoberPMWFH

 

Status:

IO - In Office

WFH - Working from Home

OOO - Out of Office

 

Current DAX to work out % "In Office"

In_Office_Percentage =
Var All_Working_Days = CALCULATE(Count('Register'[Date]), 'Register'[Status] <> "OOO", 'Register'[Status] <> BLANK())
Var In_Office = CALCULATE(Count('Register'[Date]), 'Register'[Status] = "IO")
RETURN
DIVIDE(In_Office, All_Working_Days)
1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @Natasha93 ,

According to your description, if you want to show the percentage of people who are in office each day, here's my solution.

Sample:

vkalyjmsft_1-1666602529335.png

Create a measure:

Measure =
VAR _IO =
    CALCULATE ( DISTINCTCOUNT ( Register[Agent Name] ), 'Register'[Status] = "IO" )
VAR _Total =
    DISTINCTCOUNT ( Register[Agent Name] )
RETURN
    DIVIDE ( _IO, _Total )

Get the result:

 

vkalyjmsft_2-1666602541764.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yanjiang-msft
Community Support
Community Support

Hi @Natasha93 ,

According to your description, if you want to show the percentage of people who are in office each day, here's my solution.

Sample:

vkalyjmsft_1-1666602529335.png

Create a measure:

Measure =
VAR _IO =
    CALCULATE ( DISTINCTCOUNT ( Register[Agent Name] ), 'Register'[Status] = "IO" )
VAR _Total =
    DISTINCTCOUNT ( Register[Agent Name] )
RETURN
    DIVIDE ( _IO, _Total )

Get the result:

 

vkalyjmsft_2-1666602541764.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Shaurya
Memorable Member
Memorable Member

Hi @Natasha93,

 

The formula posted by you seems to work for me.

 

In Office Percentage = 
Var All_Working_Days = CALCULATE(COUNT('Register'[Date]),('Register'[Status] <> "OOO" || 'Register'[Status] <> BLANK()))
Var In_Office = CALCULATE(COUNT('Register'[Date]),('Register'[Status] = "IO"))
RETURN
DIVIDE(In_Office, All_Working_Days)

 

Screenshot 2022-10-11 212007.jpg

 

The person was in office 50% of the time. That makes sense to me. Is there a chance that you want to calculate the percentage considering some other condition as well? If yes, some sample data would be of great help.

 

Did I answer your question? Mark this post as a solution if I did!
Consider taking a look at my blog: Forecast Period - Graphical Comparison 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.