Forum Discussion

RayMundo00's avatar
RayMundo00
Regular Visitor
3 years ago
Solved

Average and Standard Deviation using Measures as arguments

Hi everyone, hope someone can help me. Context I need to monitor the consumptions of a certain substance in a certain equipment per month. The monitoring is for the last 3 months (Consumptions tabl...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    You should be able to do this with AVERAGEX and STDEVX.S. These functions ignore blanks but include zeroes.

     

    This might work:

    AVERAGEX (
    {
        [Month -2],
        [Month -1],
        [Current Month],
        [Month +1],
        [Month +2],
        [Month +3]
    },
    [Value]
)

    but you probably don't need separate measures for each month offset when something more like this should work:

    AVERAGEX (
    GENERATESERIES ( -2, 3 ),
    CALCULATE ( [Measure], DATEADD ( 'Date'[Date], [Value], MONTH ) )
)

     

  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    You could switch between them.

     

    AVERAGEX (
    GENERATESERIES ( -2, 3 ),
    VAR _i = [Value]
    RETURN
        IF (
            _i <= 0,
            CALCULATE ( [SumCurrent], DATEADD ( 'Date'[Date], _i, MONTH ) ),
            CALCULATE ( [SumFuture],  DATEADD ( 'Date'[Date], _i, MONTH ) )
        )
)
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
3 years ago

You should be able to do this with AVERAGEX and STDEVX.S. These functions ignore blanks but include zeroes.

 

This might work:

AVERAGEX (
    {
        [Month -2],
        [Month -1],
        [Current Month],
        [Month +1],
        [Month +2],
        [Month +3]
    },
    [Value]
)

but you probably don't need separate measures for each month offset when something more like this should work:

AVERAGEX (
    GENERATESERIES ( -2, 3 ),
    CALCULATE ( [Measure], DATEADD ( 'Date'[Date], [Value], MONTH ) )
)

 

RayMundo00's avatar
RayMundo00
Regular Visitor
3 years ago

Hi AlexisOlson 

Thank you so much for your time and solution!! 

Your suggestion to use AVERAGEX/STDEVX.S worked perfectly. I used the example you gave me and that was just what I needed.

Before closing my question, you mentioned that it is probably not necessary to separate the measures for each month. I tried to adapt the example code you provided, but I am not getting the calculation right. I think it is not possible because the months -2, -1 and 0 are from the Consumption table and the months +1, +2 and +3 are from the Future Consumptions table. I don't know how to adapt this code so that the GENERATESERIES and CALCULATE consider both tables. I'm attaching an image of how the measures of each month are calculated. If you have an idea how to improve my calculations, again, I would appreciate it, because I actually need to calculate -6 months and +12 months.

 

Thanks again

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    3 years ago

    You could switch between them.

     

    AVERAGEX (
    GENERATESERIES ( -2, 3 ),
    VAR _i = [Value]
    RETURN
        IF (
            _i <= 0,
            CALCULATE ( [SumCurrent], DATEADD ( 'Date'[Date], _i, MONTH ) ),
            CALCULATE ( [SumFuture],  DATEADD ( 'Date'[Date], _i, MONTH ) )
        )
)
    • RayMundo00's avatar
      RayMundo00
      Regular Visitor
      3 years ago

      Hi AlexisOlson 

       

      Thank you so much for sharing your ideas