Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

diff between two Avg YTD Measures

Dear Community

 

 

I have on my hands two formulas that allegedly represents Avg YTD:

1st:

 

avgYTDV1_Measure = 

CALCULATE(AVERAGEX(VALUES('Date'[Monthnumber]),AVERAGE(OPS_ParetoTbl[MTBF])),
     FILTER(ALL('Date'),
        'Date'[Monthnumber] <= MAX('Date'[Monthnumber])
    )
)

 

 

 

2nd:

 

Tot_Measure = AVERAGE(OPS_ParetoTbl[MTBF])
//---------------
AvgX_Measure = AVERAGEX(VALUES('Date'[MonthNameShort]),[Tot_Measure])
//--------------
avgYTDV2_Measure = 
CALCULATE([MTBF_AvgX_Measure],
     FILTER(ALL('Date'),
        'Date'[Monthnumber] <= MAX('Date'[Monthnumber])
    )
)

 

 

 

 

Result:

MonthavgYTDV2_MeasureavgYTDV1_Measure Monthly Avg
Jan10.2110.2110.21
Feb17.9718.5325.73
Mar22.2922.2930.94
Apr24.9925.1633.09
May30.8529.6954.25

 

while [avgYTDV2_Measure] is technically the correct one why is [avgYTDV1_Measure] isn't giving correct numbers??

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    You can get the answer from the following blog:

    Caution When Using Variables in DAX and Power BI

    You can also update the formula of measure [avgYTDV1_Measure] as below to get the correct result:

     

    avgYTDV1_Measure =
    VAR _avg =
        CALCULATE ( AVERAGE ( OPS_ParetoTbl[MTBF] ) )
    RETURN
        CALCULATE (
            AVERAGEX ( VALUES ( 'Date'[MonthNameShort] ), _avg ),
            FILTER ( ALL ( 'Date' ), 'Date'[Monthnumber] <= MAX ( 'Date'[Monthnumber] ) )
        )

     

    Best Regards

4 Replies

  • Anonymous , we should always use calculate in x functions  , if we are not using a measure

     

     

     

    avgYTDV1_Measure = 
    
    CALCULATE(AVERAGEX(VALUES('Date'[Monthnumber]),calculate(AVERAGE(OPS_ParetoTbl[MTBF]))),
         FILTER(ALL('Date'),
            'Date'[Monthnumber] <= MAX('Date'[Monthnumber])
        )
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      amitchandak Thank you for your prompt reply,

       

      however, while calculate got me closer  to the right answer its still not an exact YTD average

      • amitchandak's avatar
        amitchandak
        Super User

        Anonymous , the second one usages monthnameshort , please check if that can make a diff. Also can share what are you getting now with the formula's

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

    You can get the answer from the following blog:

    Caution When Using Variables in DAX and Power BI

    You can also update the formula of measure [avgYTDV1_Measure] as below to get the correct result:

     

    avgYTDV1_Measure =
    VAR _avg =
        CALCULATE ( AVERAGE ( OPS_ParetoTbl[MTBF] ) )
    RETURN
        CALCULATE (
            AVERAGEX ( VALUES ( 'Date'[MonthNameShort] ), _avg ),
            FILTER ( ALL ( 'Date' ), 'Date'[Monthnumber] <= MAX ( 'Date'[Monthnumber] ) )
        )

     

    Best Regards