utilizing CALCULATE and ALL to calculate value per sqft

[vbaisdaxing]

tblProperty
[TABLE="width: 500"]
<tbody>[TR]
[TD]id[/TD]
[TD]property type[/TD]
[TD]sqft[/TD]
[TD]property value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]apartment[/TD]
[TD]100[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]industrial[/TD]
[TD]100[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]office[/TD]
[TD]100[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]apartment[/TD]
[TD]300[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]industrial[/TD]
[TD]200[/TD]
[TD]6000[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]office[/TD]
[TD]100[/TD]
[TD]70000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]apartment[/TD]
[TD]100[/TD]
[TD]80000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]industrial[/TD]
[TD]100[/TD]
[TD]9000[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]office[/TD]
[TD]100[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]apartment[/TD]
[TD]200[/TD]
[TD]11000[/TD]
[/TR]
</tbody>[/TABLE]

PivotTable
[TABLE="width: 500"]
<tbody>[TR]
[TD]row labels[/TD]
[TD]sum of sqft[/TD]
[TD]sum of property value[/TD]
[TD]value/per sqft[/TD]
[/TR]
[TR]
[TD]apartment[/TD]
[TD]700[/TD]
[TD]106000[/TD]
[TD]151.43[/TD]
[/TR]
[TR]
[TD]industrial[/TD]
[TD]400[/TD]
[TD]2000[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]office[/TD]
[TD]300[/TD]
[TD]84000[/TD]
[TD]280[/TD]
[/TR]
</tbody>[/TABLE]









Hello everyone,

I have a table called tblProperty. I am trying to create/utilize a DAX formula that can help me calculate the value/per sqft. I'm assuming some combination of using CALCULATE and ALL is probably the right direction in attaining my desired results. Any suggestions will be greatly appreciated. Thanks in advance!!!

 

[citizenbh]

Create three measures:

Sum of sqft:=sum([sqft])
Sum of pv:=SUM([property value])
pv/sqft:=[Sum of pv]/[Sum of sqft]

and in Pivot table add in

Rows: property type
Σ Values: Sum of sqft, Sum of pv, pv/sqft

 

[SyedUsman]

Create The following measure

VALUE PER SQFT:=SUM([property value])/SUM([sqft])

 

[vbaisdaxing]

Thank you very much fellas. I feel like the biggest idiot for not knowing that lol.

 

[olofmur]

Also, whenever you are dealing with division you should use the DIVIDE() function to protect yourself from #DIV/0! errors.