IF Formula issue

[Anonymous]

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

[JoHo_BI]

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]

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]

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

[Anonymous]

Can you please write the full formula including the IF calculated column you mentioned as I am not sure what you mean?

Many thanks

[JoHo_BI]

No problem.

So your if statement works fine, you just need to add the previous MEASURE:

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

You'll just need to change the items in bold to your specific dataset columns.

If that doesn't work, could you possibly share a pbix file?