Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 t...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

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

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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

 

Regards,

Xiaoxin Sheng