Forum Discussion

dylen's avatar
dylen
Regular Visitor
2 years ago
Solved

How to find occupancy percantage

Hi, 

 

I am trying to show our Occupancy total as a number and as a percentage. 

Ideally I would like to replicate this formula from excel but no luck = IF(Operational places>0,(1-(Vacancies/Operational places)),0) 

 

Currently, the Occupancy as a number measure (column below) is working correctly, but when shown as a percentage, it returns incorrect values. 

 

Below is the columns I am referencing:

 

 

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi dylen - Create below measures as may be if you want to show occupancy count use the first measure and for percentage occupancy use the second measure :

     

    Measure 1: Occupancy Number = IF(SUM('Table'[Operational places]) > 0, (1 - (SUM('Table'[Vacancies]) / SUM('Table'[Operational places]))), 0)

     

    Measure 2: Occupancy Percentage = IF(SUM('Table'[Operational places]) > 0, (1 - (SUM('Table'[Vacancies]) / SUM('Table'[Operational places]))), 0) * 100

     

    use format, to display the values in percentage.Now it is possible to display the percentage occupancy 

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi dylen - Create below measures as may be if you want to show occupancy count use the first measure and for percentage occupancy use the second measure :

     

    Measure 1: Occupancy Number = IF(SUM('Table'[Operational places]) > 0, (1 - (SUM('Table'[Vacancies]) / SUM('Table'[Operational places]))), 0)

     

    Measure 2: Occupancy Percentage = IF(SUM('Table'[Operational places]) > 0, (1 - (SUM('Table'[Vacancies]) / SUM('Table'[Operational places]))), 0) * 100

     

    use format, to display the values in percentage.Now it is possible to display the percentage occupancy 

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Your solutions is great rajendraongole1 , it work fine.
    Hi, dylen 

    Have you solved the current problem? If yes, you can mark a helpful reply as a solution so that others in the community can quickly find a solution when they encounter the same problem. If you don't, you can try the following DAX expressions:

    Occupancy = 
VAR Operational_places = SELECTEDVALUE('Table'[Operational places])
VAR vancancies = SELECTEDVALUE('Table'[Vancancies])
RETURN IF(Operational_places>0,1-DIVIDE(vancancies,Operational_places),0)

    Here are the results:

    Set the result as a percentage:

    I've provided the PBIX file used this time below.

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

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