Forum Discussion

lilySixteen's avatar
lilySixteen
Helper III
6 years ago
Solved

Vacancy and Occupancy rate caculation

Hello community,

 

Below is my partial Tenant table that has a list of Properties and its associated units,  tenants, and unit square footage. Some units are not rented so it shows VACANT in the Tenant Name column. 

I want to calculate:

Vacancy% =  total Unit Square Footage of the Vacant units / total Unit Square Footage of the property  and

Occupancy%= total Unit Square Footage of the non-Vacant units / total Unit Square Footage of the property

 

Could you please help me on this? Really appreciate it!!

 

 

 
 

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    lilySixteen , Try like

    Vacancy% = divide(calculate(Sum(Table[Unit Sqrt Feet]), filter(Table,Table[tenant name]="VACANT")),Sum(Table[Unit Sqrt Feet]))

Occupancy%= divide(calculate(Sum(Table[Unit Sqrt Feet]), filter(Table,Table[tenant name]<>"VACANT")),Sum(Table[Unit Sqrt Feet]))

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    lilySixteen , Try like

    Vacancy% = divide(calculate(Sum(Table[Unit Sqrt Feet]), filter(Table,Table[tenant name]="VACANT")),Sum(Table[Unit Sqrt Feet]))

Occupancy%= divide(calculate(Sum(Table[Unit Sqrt Feet]), filter(Table,Table[tenant name]<>"VACANT")),Sum(Table[Unit Sqrt Feet]))
    • lilySixteen's avatar
      lilySixteen
      Helper III
      6 years ago

      amitchandak Thank you for providing your answers so quickly to my both questions (including another rate calc that I posted earlier than this one)!

    • lilySixteen's avatar
      lilySixteen
      Helper III
      6 years ago

      Hi amitchandak The only issue with these formulas is that, if a property doesn't have a vacant tenant, the vacancy% should be 0% but now it returns nothing. Is it possible to add a condition for the zero vacancy case? Thank you!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi lilySixteen ,

     

    Try this measure

    VacanyPercent =
VAR TotalArea =
    CALCULATE (
        SUM ( 'Table'[unitssquarefootage] ),
        ALL ( 'Table' )
    )
VAR _Vacant =
    CALCULATE (
        SUM ( 'Table'[unitssquarefootage] ),
        Table[TenantName] = "VACANT"
    )
RETURN
    DIVIDE (
        _Vacant,
        TotalArea
    )

     

     

    Occupancy =
VAR TotalArea =
    CALCULATE (
        SUM ( 'Table'[unitssquarefootage] ),
        ALL ( 'Table' )
    )
VAR _Vacant =
    CALCULATE (
        SUM ( 'Table'[unitssquarefootage] ),
        Table[TenantName] <> "VACANT"
    )
RETURN
    DIVIDE (
        _Vacant,
        TotalArea
    )

     

    Regards,

    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

    • lilySixteen's avatar
      lilySixteen
      Helper III
      6 years ago

      Anonymous Thanks so much for your help! I tried the solution that it appears the calculation is not accurate. But really appreciate your answers.