Forum Discussion

curtismob's avatar
curtismob
Helper IV
5 years ago

Exclude Blank Measure Values From Average Calculation

Hello, I am looking for some direction on excluding blank measure values from my average cost per square foot calculation.  I am not using AVERAGE, because I have to do the cost per sqft calculation ...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

You may download my PBI file from here.

Hope this helps.

curtismob's avatar
curtismob
Helper IV
5 years ago

Ashish_Mathur, 

Thanks again for the response.  I used very simple data for my example, possibly too simple.  Unfortunately, if the data is changed by adding another product in each divison and both cost and square feet numbers are changed,so that all cost per sqft doesn't come out to 10.00, then the suggested changes do not appear to work.

 

Total area = SUM(CostPerSqft[SQFT])
Total cost = SUM(CostPerSqft[Cost])
Cost per square foot = DIVIDE([Total cost],[Total area])
 
The average totals appear to be close, but are still off.