Forum Discussion

GilesWalker's avatar
GilesWalker
Skilled Sharer
10 years ago
Solved

Help calculating cumulative data

Hi Everyone,   I have a table where I need to calculate the cumulative of Forestry total wagons column in the data below. The number in this column is derived from multiplying columns forestry wago...
Need Help
  • GilesWalker's avatar
    GilesWalker
    10 years ago

    Thanks for the help everyone, unfortunately I didnt get the right answer from your responses. The table I am using is just an excel file I manually created so I went in there and for the end of each month using an if formula I created a one off total wagons number. This worked perfectly.

     

    Thanks for your help though.

Rémi's avatar
Rémi
Resolver III
10 years ago

Hi,

 

Can you try this measure :

 

Cumulative Measure = SUMX( FILTER( SUMMARIZE( YourTable , [Month] , "Total wagons" , AVERAGE( [Forestry total wagons] ) , "Max Date" , MAX( [Date Key] ) , [Max Date]<=MAX([Date Key]) ) , [Total wagons] )

  • konstantinos's avatar
    konstantinos
    Memorable Member
    10 years ago

    Hi also this works..I think Rémi must be faster 

     

    cummulative :=
CALCULATE (
    SUMX (
        VALUES ( Table1[Months] );
        AVERAGEX ( Table1; Table1[Forestry total wagons] )
    );
    FILTER ( ALL ( Table1 ); Table1[Date key] <= MAX ( Table1[Date key] ) )
)

    Edit : You don't need the AVERAGEX iteration..simple Average

     

    cummulative :=
CALCULATE (
    SUMX ( VALUES ( Table1[Months] ); AVERAGE ( Table1[Forestry total wagons] ) );
    FILTER ( ALL ( Table1 ); Table1[Date key] <= MAX ( Table1[Date key] ) )
)
    • GilesWalker's avatar
      GilesWalker
      Skilled Sharer
      10 years ago

      Thanks for the help everyone, unfortunately I didnt get the right answer from your responses. The table I am using is just an excel file I manually created so I went in there and for the end of each month using an if formula I created a one off total wagons number. This worked perfectly.

       

      Thanks for your help though.