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
curtismob's avatar
curtismob
Helper IV
5 years ago

Anonymous,

 

Thank you for the response.  Can you provide an "if" statement example using the measures I provide initially?  Everything I have tried doesn't appear to work.

 

Thank you,

Curtis

Anonymous's avatar
Anonymous
Not applicable
5 years ago

HI curtismob,

You can try to use the following measure formula, I added the if statement to check hierarchy level and write a formula for the calculation on measure total level:

_mAvg Cost Per SQFT per Product = 
VAR detailLevel =
    CALCULATE (
        DIVIDE (
            DIVIDE ( [_mCost Per SQFT], [_mDistinctCount YYYMM], 0 ),
            DISTINCTCOUNT ( [Product] ),
            0
        )
    )
VAR totalLevel =
    DIVIDE (
        CALCULATE (
            SUM ( 'CostPerSqft'[_cCost Per SQFT] ),
            ALLSELECTED ( 'CostPerSqft' )
        ),
        SUMX (
            SUMMARIZE (
                CostPerSqft,
                [Area],
                [Division],
                [Product],
                "DC", DISTINCTCOUNT ( CostPerSqft[_cYYYY Mon] )
            ),
            [DC]
        ),
        0
    )
RETURN
    IF (
        ISINSCOPE ( CostPerSqft[_cYYYY Mon] ),
        detailLevel,
        IF ( ISINSCOPE ( CostPerSqft[Division] ), detailLevel, totalLevel )
    )

Regards,

Xiaoxin Sheng