Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculating Cumulative total of average using DAX expression

Hello there, 

 

I am stuck with one scenario and having tough time figuring out the running total of averages using DAX expression, i would be glad if anyone could help me out on this, 

below is the scenarios,

 

YEAROVC_ValueAverage of OVC_ValueExpectedColumn: Cumulative Average
20151055603.61466.1161111466.116111
201651279418.5926763.7884128229.90452
2017409404085.2145799.1756175495.1963
2018798181874.5264473.7822438502.8623
20191243794804412125.5151850628.3774
20202303576361763279.11241613907.49
202131633177481048150.3472662057.837
202231980712211059665.7463721723.583
20232824829664935993.92444657717.507
20241342139881451898.94975109616.457
202517620535.637667.7700735117284.227

 

as we can see there are different OVC_Value for different years, 3rd column 'Average of OVC_Value' is the instance of first OVC_VALUE and i have applied Average Data operation on it,  what i need is 4th Column, i.e. ExpectedColumn: Cumulative Average which will calculate the values of average cumulatively as displayed in column,

 

is there any way to figure this out with DAX expression, i am sure there should be one but i am having hard time to figure that out, 

 

any help would be greatly appreciated, Thanks alot in advance,

 

Regards, 
Malav

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous,

 

After play with your data, I create a measure to calculate cumulative average:

Measure =
VAR currYear =
    MAX ( OVC_data[OVC_YEAR] )
RETURN
    SUMX (
        FILTER (
            SUMMARIZE (
                ALLSELECTED ( OVC_data ),
                [OVC_YEAR],
                "Total Value", SUM ( OVC_data[OVC_Value] ),
                "AVG Value", AVERAGE ( OVC_data[OVC_Value] )
            ),
            [OVC_YEAR] <= currYear
        ),
        [AVG Value]
    )

11.PNG

 

Regards,

Xiaoxin Sheng

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

HI @Anonymous,

 

You can try to use below column formula to get cumulative average value from summary table.

 

Column formula:

Calculate column =
SUMX (
    FILTER (
        SUMMARIZE (
            Table,
            [Year],
            "Total Value", SUM ( Table[OVC_Value] ),
            "AVG Value", AVERAGE ( Table[OVC_Value] )
        ),
        [Year] <= Table[Year]
    ),
    [AVG Value]
)

 

If above not help, please share some sample data for test and coding formula.

 

Regards,

Xiaoxin Sheng

Anonymous
Not applicable

Hi Xiaoxin Sheng, 

 

I tried the given solution but it did not work because i belive that while adding cumulative average it is also adding the cumulative counts of values and as a result the cumulative average keeps in decreasing, i am attaching the database, thank you very much for helping me on this... 

 

https://files.fm/u/3ma7vse6

 

Regards,

Malav 

 

 

Anonymous
Not applicable

Hi @Anonymous,

 

After play with your data, I create a measure to calculate cumulative average:

Measure =
VAR currYear =
    MAX ( OVC_data[OVC_YEAR] )
RETURN
    SUMX (
        FILTER (
            SUMMARIZE (
                ALLSELECTED ( OVC_data ),
                [OVC_YEAR],
                "Total Value", SUM ( OVC_data[OVC_Value] ),
                "AVG Value", AVERAGE ( OVC_data[OVC_Value] )
            ),
            [OVC_YEAR] <= currYear
        ),
        [AVG Value]
    )

11.PNG

 

Regards,

Xiaoxin Sheng

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors