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
    8 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 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'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
    8 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