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

 

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

3 Replies

  • 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