Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

IF Formula issue

Hello,

 

I am working in a real estate dashboard and I am having an issue when I try to create a formula which calculates the total of unit size for the vacant units in the buildings.

 

I am creating the formula below 

 

Vacant area (sq ft) = if([Vacant/used]="Vacant",[Unit size (sq ft)],0)

 

The formula seems to work as it shows the number of the unit size everytime I have a vacant unit and 0 when the unit is used.

However, when I use the formula in my dashboard using a "Card" visual to summarise the total of vacant unit size, I get 0 as a result.

 

Can you please give some support with this?

 

Many thanks,

MariosChr90

 

 

 

 

Need Help

5 Replies

  • JoHo_BI's avatar
    JoHo_BI
    Responsive Resident
    8 years ago

    Hi Marios, 

     

    Your calculated column works great on a row by row basis, as each column can be checked to see if vacant or not. 

     

    For aggregations, you'll need a measure. Write a new measure along the lines of:

     

    Total SqFt = CALCULATE(SUM(Table[Unit size (sq ft)], Table[Vacant/Used] = "Vacant")

     

    You will need to replace table with the actual table names.

     

    Hope that helps! 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hello JoHo_BI,

       

      Many thanks for your response. I am trying to apply your formula but it cannot accept Table[Vacant/Used] = "Vacant", as in this part of the formula it only accepts measure fields.

       

      Any idea on how to overcome this?

       

      • JoHo_BI's avatar
        JoHo_BI
        Responsive Resident
        8 years ago

        You need to make the 'IF' calculation a calculated column for it to work.