Forum Discussion

bourne2000's avatar
bourne2000
Icon for Helper V rankHelper V
4 years ago
Solved

How to calculate average cumulative?

Hi

 

I am having below table

 

 

Here the average column is a measure. Below caulcuation I did 

 

Average Max = AVERAGE(Append1[Max_Price])
Average Min = AVERAGE(Append1[Min_Price])
 Average = ([Average Max] + [Average Min]) / 2
 
Now, I want to calculate the cumulative average of average column
 
I need output as below 
 

I want to calculate the average cumulative value . Need output as below. I did this calculation in Excel and sample excel file attached here https://we.tl/t-N2e7mLeMaF

Cumulative Average is 1045.428571 for 30th Sep is average of all the values. For 29 sep,  cumulative average is 1046.83333 is average of values from 29 sep to 30 sep. Same is applicable for other dates.

 

PBIX file attached here https://we.tl/t-0g7kEMYd7O

Need Help
Show and Tell
  • TheoC's avatar
    TheoC
    4 years ago

    bourne2000 please create a Calculated Column and call it "Average".

     

    Average = ( SUM ( 'Table'[Min Price (Avg)] ) + SUM ( 'Table'[Max Price (Avg)] ) / 2

     

    From here, please use the following measure:

     

    Measure = TOTALYTD ( SUM ( Table[Average] ) , 'Table1'[Date] )

     

    Once you have the above done, drag the measure into your Table visual and it should work.


    Let me know how it all goes 🙂

     

     

8 Replies

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi bourne2000 

     

    Create a measure as follows:

     

    Measure = TOTALYTD ( SUM ( Table1[Average] ) , 'Table1'[Date] )

    The output will be the cumulative average as below.

     

    I've used variables in my example in the event you want to expand on the measure, etc.  However, no need to change the Measure presented above if there's no reason to expand.

     

     

    Hope this helps 🙂

    • bourne2000's avatar
      bourne2000
      Icon for Helper V rankHelper V
      4 years ago

      TheoC  Thanks

       

      I am not able to get the average in the dax measure. My average column is a measure. 

       

       

      It's not coming. Can you guide me?

       

       

       

       

      • TheoC's avatar
        TheoC
        Icon for Community Champion rankCommunity Champion
        4 years ago

        bourne2000 do you have a column called "Average"?  If so, you should be able to copy and paste the following:

         

        Measure = TOTALYTD ( SUM ( 'Append1'[Average] ) , 'Append1'[Date] )