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,

In my version of the file, the average is 25.28.  That seems correct.  Drag the other 2 measures to the visual and export the data to MS Excel.  Add the Total cost and Total Area column and divide the 2.  The result will be 25.28.

curtismob's avatar
curtismob
Helper IV
5 years ago

Please recall my original post:

 

_mAvg Cost Per SQFT per Product =
CALCULATE(DIVIDE(DIVIDE('CostPerSqft'[_mCost Per SQFT],'CostPerSqft'[_mDistinctCount YYYMM], 0), DISTINCTCOUNT('CostPerSqft'[Product]), 0))
 
Unfortunately, your solution does not take into account the separate Products.
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    It does.  Please recheck.

  • curtismob's avatar
    curtismob
    Helper IV
    5 years ago

    Thanks for your efforts on this.