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])), ...
Need Help
Show and Tell
  • 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

amitchandak's avatar
amitchandak
Super User
5 years ago

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
    5 years ago

    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
      5 years ago

      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