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 first.  See example below.  The desired result would be for the total average to be 10.00, but the Division 1/Product A Janurary thru June months w/no data, is causing the total average to be 7.50 instead.  This is all driven by the date slicer.  If the slicer is changed to 7/1/2020 thru 12/31/2020, then it works as ecpected with a total average of 10.00.  The below is a simplified version of my actual data and I have the pbix for the example below, I'm just not sure how to upload or link it.  I provided the simple table of data, along with my calculated columns and measures.

 

Any help would be greatly appreciated, 

curtismob

 

DivisionProductSQFTYearMonthCost
Division 1ProductA102020 Jul100
Division 1ProductA102020 Aug100
Division 1ProductA102020 Sep100
Division 1ProductA102020 Oct100
Division 1ProductA102020 Nov100
Division 1ProductA102020 Dec100
Division 2ProductB102020 Jan100
Division 2ProductB102020 Feb100
Division 2ProductB102020 Mar100
Division 2ProductB102020 Apr100
Division 2ProductB102020 May100
Division 2ProductB102020 Jun100
Division 2ProductB102020 Jul100
Division 2ProductB102020 Aug100
Division 2ProductB102020 Sep100
Division 2ProductB102020 Oct100
Division 2ProductB102020 Nov100
Division 2ProductB102020 Dec100

 

Calulated columns and measures:

_cArea = "Area 1"  (This was added to the table after the fact, to add a level to the heirarchy)
_cCost Per Sqft = DIVIDE(CostPerSqft[Cost], CostPerSqft[SQFT], 0)
_cYYYY Mon = FORMAT(CostPerSqft[YearMonth].[Date], "YYYY MMM")
_cYYYYMM = FORMAT(CostPerSqft[YearMonth].[Date], "YYYYMM")
_mDistinctCount YYYMM = DISTINCTCOUNT('CostPerSqft'[_cYYYY Mon])
_mCost Per SQFT =
VAR
BegSnapshotDate = MIN(CostPerSqft[YearMonth].[Date])
VAR
EndSnapshotDate = MAX(CostPerSqft[YearMonth].[Date])
RETURN
CALCULATE(SUM('CostPerSqft'[_cCost Per SQFT]),
FILTER('CostPerSqft',
'CostPerSqft'[YearMonth].[Date] >= BegSnapshotDate &&
'CostPerSqft'[YearMonth].[Date] <= EndSnapshotDate))
_mAvg Cost Per SQFT per Product =
CALCULATE(DIVIDE(DIVIDE('CostPerSqft'[_mCost Per SQFT],'CostPerSqft'[_mDistinctCount YYYMM], 0), DISTINCTCOUNT('CostPerSqft'[Product]), 0))
 

 

 

 

Need Help

18 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    curtismob , This formula seems fine, remove 0 from first divide and check

    CALCULATE(DIVIDE(DIVIDE([_mCost Per SQFT],[_mDistinctCount YYYMM]), DISTINCTCOUNT('CostPerSqft'[Product]), 0))

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI curtismob,

    Did Ashish_Mathur's formula help for your scenario? If this is a case, you can consider accepting his suggestion to help others who faced a similar requirement to find it more quickly.

    If not, you can feel free to post here with detailed descriptions to help us clarify your scenario.

    Regards,

    Xiaoxin Sheng