Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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!
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?
You need to make the 'IF' calculation a calculated column for it to work.
Can you please write the full formula including the IF calculated column you mentioned as I am not sure what you mean?
Many thanks
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |