Cumulative average per week

[Syndicate_Admin]

hello
I need your help to create a measure and thus obtain the value of the accumulated average per week, this measure I have tried to create in many ways without any success. My tbla is configured as follows with the following measurements:

Average condition = SUM('Sampling Indicators'[sumCondition])/SUM('Process Sampling' [General Count]) *100

Average soft fruit indicator =SUM('Indicator Sampling'[sumSoftfruit])/SUM('Process Sampling'[ConteoGeneral]) *100

Average Linear Indicator = ([Average condition]+ [Average Soft Fruit Gauge])/2

I leave Excel with TBLA data structure
Indicadores.xlsx sampling

The expected result
for week 44: 91.67)/1
for week 45: (91.67+ 92.40)/2
for week 46: (91.67+ 92.40+95.46)/3
and so on...

I thank you for all your support and experience, thank you!

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

One of ways to achieve this is to use WINDOW DAX function in the measure.

WINDOW function (DAX) - DAX | Microsoft Learn

expected result: =
VAR _t =
    ADDCOLUMNS (
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ALL ( 'calendar'[Year-Month], 'calendar'[Year-Month sort] ),
            ORDERBY ( 'calendar'[Year-Month sort], ASC )
        ),
        "#avgsalespermonth", [average sales per month:]
    )
VAR _countrows =
    COUNTROWS ( _t )
RETURN
    IF (
        NOT ISBLANK ( [average sales per month:] ),
        SUMX ( _t, [#avgsalespermonth] ) / _countrows
    )

[Anonymous · Accepted solution]

Hi @Syndicate_Admin ,

I very much recognize Jihwan_Kim's idea, but I created a form just the same.

So I think you can create a calculated column and here is the DAX code.

Column = 
VAR CurrentRow = 'Table'[Number]
RETURN
    DIVIDE(
        SUMX(
            FILTER(
                'Table',
               'Table'[Number] <= CurrentRow
            ),
            'Table'[Value]
        ),
        CurrentRow
    )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

One of ways to achieve this is to use WINDOW DAX function in the measure.

WINDOW function (DAX) - DAX | Microsoft Learn

expected result: =
VAR _t =
    ADDCOLUMNS (
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ALL ( 'calendar'[Year-Month], 'calendar'[Year-Month sort] ),
            ORDERBY ( 'calendar'[Year-Month sort], ASC )
        ),
        "#avgsalespermonth", [average sales per month:]
    )
VAR _countrows =
    COUNTROWS ( _t )
RETURN
    IF (
        NOT ISBLANK ( [average sales per month:] ),
        SUMX ( _t, [#avgsalespermonth] ) / _countrows
    )