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
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi curtismob,

I think this may be related to your measure formula who setting the 0 in divide functions. You can add an if statement to check the current row count to confirm they not blank.

Handling BLANK in DAX 

Regards,

Xiaoxin Sheng

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