cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GilesWalker
Skilled Sharer
Skilled Sharer

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 wagons and forestry services. For example in September the max number of wagons is 189. In October It was 198 for some of the month and 117 for the rest, average is 179. Therefore the cumulative number for these two months is 377. I cant seem to get this answer though. Hope you can help.

 

Date keyYearMonthDaysTotal daysWeekForestry servicesMonthsQuarterForestry wagonsForestry target tonnesForestry wagon KPI tonnesForestry total wagons
1/09/2015201591303621SepQ39623732189
2/09/2015201592303621SepQ39623732189
3/09/2015201593303621SepQ39623732189
4/09/2015201594303621SepQ39623732189
5/09/2015201595303621SepQ39623732189
6/09/2015201596303721SepQ39623732189
7/09/2015201597303721SepQ39623732189
8/09/2015201598303721SepQ39623732189
9/09/2015201599303721SepQ39623732189
10/09/20152015910303721SepQ39623732189
11/09/20152015911303721SepQ39623732189
12/09/20152015912303721SepQ39623732189
13/09/20152015913303821SepQ39623732189
14/09/20152015914303821SepQ39623732189
15/09/20152015915303821SepQ39623732189
16/09/20152015916303821SepQ39623732189
17/09/20152015917303821SepQ39623732189
18/09/20152015918303821SepQ39623732189
19/09/20152015919303821SepQ39623732189
20/09/20152015920303921SepQ39623732189
21/09/20152015921303921SepQ39623732189
22/09/20152015922303921SepQ39623732189
23/09/20152015923303921SepQ39623732189
24/09/20152015924303921SepQ39623732189
25/09/20152015925303921SepQ39623732189
26/09/20152015926303921SepQ39623732189
27/09/20152015927304021SepQ39623732189
28/09/20152015928304021SepQ39623732189
29/09/20152015929304021SepQ39623732189
30/09/20152015930304021SepQ39623732189
1/10/20152015101314022OctQ49653432198
2/10/20152015102314022OctQ49653432198
3/10/20152015103314022OctQ49653432198
4/10/20152015104314122OctQ49653432198
5/10/20152015105314122OctQ49653432198
6/10/20152015106314122OctQ49653432198
7/10/20152015107314122OctQ49653432198
8/10/20152015108314122OctQ49653432198
9/10/20152015109314122OctQ49653432198
10/10/201520151010314122OctQ49653432198
11/10/201520151011314222OctQ49653432198
12/10/201520151012314222OctQ49653432198
13/10/201520151013314222OctQ49653432198
14/10/201520151014314222OctQ49653432198
15/10/201520151015314222OctQ49653432198
16/10/201520151016314222OctQ49653432198
17/10/201520151017314222OctQ49653432198
18/10/201520151018314322OctQ49653432198
19/10/201520151019314322OctQ49653432198
20/10/201520151020314322OctQ49653432198
21/10/201520151021314322OctQ49653432198
22/10/201520151022314322OctQ49653432198
23/10/201520151023314322OctQ49653432198
24/10/201520151024314322OctQ49653432198
25/10/201520151025314413OctQ49386132117
26/10/201520151026314413OctQ49386132117
27/10/201520151027314413OctQ49386132117
28/10/201520151028314413OctQ49386132117
29/10/201520151029314413OctQ49386132117
30/10/201520151030314413OctQ49386132117
31/10/201520151031314413OctQ49386132117
1 ACCEPTED SOLUTION

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.

View solution in original post

3 REPLIES 3
Rémi
Resolver III
Resolver III

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

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

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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!