Average and Standard Deviation using Measures as arguments

[RayMundo00]

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

[AlexisOlson · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

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]

Hi @AlexisOlson 

Thank you so much for sharing your ideas