Calculate average depending on the number of month

[ghc13]

Hi,

i try for hours to solve this, but i can´t find a solution. Here is an example what i want to achive:

Month    Value    Average

Jan           2             2                     = 2 / 1

Feb          5             3.5                   = (2+5) / 2

Mar         1             2.666               = (2 + 5 +1) / 3 

...

Goal is to calculate the average column. I am thankful for you advice.

Regards George

[v-yaningy-msft]

Hi, @ghc13 

Based on your description, I have created a New Column to achieve the effect you are looking for. Following picture shows the effect of the display.

New Column:

Average =

VAR _totalizingValue =

    CALCULATE (

        SUM ( 'Table'[Value] ),

        FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) )

    )

VAR _average =

    DIVIDE ( _totalizingValue, 'Table'[Index] )

RETURN

    _average

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[ghc13]

Thx Yang you helped me a lot. Your solution works. The icing of the cake would be, if it is possible to create the result without the index column.

[v-yaningy-msft · Accepted solution]

Hi, @ghc13 

Depending on your needs, you need to use the number of rows in the current row as the divisor. If your Month column can be converted to a number, then you can leave out the Index column; the current version does not support getting the number of rows in the current row directly, you can submit an idea for here and the product team will take note of such a need.

Related Link: New tab (powerbi.com)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[saurabhtd]

@ghc13 This logic may help you.  replace YourTable with the actual name of your table.

Average Depending on Month Count :=
VAR CurrentMonth = MONTH(SELECTEDVALUE('YourTable'[Month]))
VAR MonthsElapsed =
    CALCULATE(
        COUNTROWS('YourTable'),
        FILTER('YourTable', 'YourTable'[Month] <= CurrentMonth)
    )
RETURN
    DIVIDE(
        SUMX(
            FILTER('YourTable', 'YourTable'[Month] <= CurrentMonth),
            [Value]
        ),
        MonthsElapsed
    )

[ghc13]

Thx for your fast response. I understand what the query do. I tested it, but the Filter-Expression returns an empty column. CurrentMonth is also empty except i don´t use the selectvalue funktion, then i get 1 ... 12.