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 table), the current month and the next 3 months (future consumptions table). In addition, I need to calculate the average and the standard deviation.

To calculate the average and standard deviation, the calculation must be done on the months that have value, that is, the calculation must NOT consider the nulls, for that, I manually calculated the average and standard deviation measures.

*******************

**********************

 

*********************************************

This is the resulting table

 

The calculation is correct and works, but I have hundreds of thousands of data, so I have performance issues when loading the visual.

My question is: Is there a different approach I can use to solve my problem? Is there another way to calculate the average and standard deviation using DAX formulas (AVERAGE, STDEV.S with measures as arguments)?

I attach a file with data as an example

NOTES: I'm using DirectQuery (Power BI dataset)

I would really appreciate your help

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 ) )
        )
)

4 Replies

  • 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 ) )
        )
)