Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 key | Year | Month | Days | Total days | Week | Forestry services | Months | Quarter | Forestry wagons | Forestry target tonnes | Forestry wagon KPI tonnes | Forestry total wagons |
1/09/2015 | 2015 | 9 | 1 | 30 | 36 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
2/09/2015 | 2015 | 9 | 2 | 30 | 36 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
3/09/2015 | 2015 | 9 | 3 | 30 | 36 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
4/09/2015 | 2015 | 9 | 4 | 30 | 36 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
5/09/2015 | 2015 | 9 | 5 | 30 | 36 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
6/09/2015 | 2015 | 9 | 6 | 30 | 37 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
7/09/2015 | 2015 | 9 | 7 | 30 | 37 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
8/09/2015 | 2015 | 9 | 8 | 30 | 37 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
9/09/2015 | 2015 | 9 | 9 | 30 | 37 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
10/09/2015 | 2015 | 9 | 10 | 30 | 37 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
11/09/2015 | 2015 | 9 | 11 | 30 | 37 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
12/09/2015 | 2015 | 9 | 12 | 30 | 37 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
13/09/2015 | 2015 | 9 | 13 | 30 | 38 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
14/09/2015 | 2015 | 9 | 14 | 30 | 38 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
15/09/2015 | 2015 | 9 | 15 | 30 | 38 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
16/09/2015 | 2015 | 9 | 16 | 30 | 38 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
17/09/2015 | 2015 | 9 | 17 | 30 | 38 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
18/09/2015 | 2015 | 9 | 18 | 30 | 38 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
19/09/2015 | 2015 | 9 | 19 | 30 | 38 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
20/09/2015 | 2015 | 9 | 20 | 30 | 39 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
21/09/2015 | 2015 | 9 | 21 | 30 | 39 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
22/09/2015 | 2015 | 9 | 22 | 30 | 39 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
23/09/2015 | 2015 | 9 | 23 | 30 | 39 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
24/09/2015 | 2015 | 9 | 24 | 30 | 39 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
25/09/2015 | 2015 | 9 | 25 | 30 | 39 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
26/09/2015 | 2015 | 9 | 26 | 30 | 39 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
27/09/2015 | 2015 | 9 | 27 | 30 | 40 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
28/09/2015 | 2015 | 9 | 28 | 30 | 40 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
29/09/2015 | 2015 | 9 | 29 | 30 | 40 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
30/09/2015 | 2015 | 9 | 30 | 30 | 40 | 21 | Sep | Q3 | 9 | 6237 | 32 | 189 |
1/10/2015 | 2015 | 10 | 1 | 31 | 40 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
2/10/2015 | 2015 | 10 | 2 | 31 | 40 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
3/10/2015 | 2015 | 10 | 3 | 31 | 40 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
4/10/2015 | 2015 | 10 | 4 | 31 | 41 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
5/10/2015 | 2015 | 10 | 5 | 31 | 41 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
6/10/2015 | 2015 | 10 | 6 | 31 | 41 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
7/10/2015 | 2015 | 10 | 7 | 31 | 41 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
8/10/2015 | 2015 | 10 | 8 | 31 | 41 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
9/10/2015 | 2015 | 10 | 9 | 31 | 41 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
10/10/2015 | 2015 | 10 | 10 | 31 | 41 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
11/10/2015 | 2015 | 10 | 11 | 31 | 42 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
12/10/2015 | 2015 | 10 | 12 | 31 | 42 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
13/10/2015 | 2015 | 10 | 13 | 31 | 42 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
14/10/2015 | 2015 | 10 | 14 | 31 | 42 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
15/10/2015 | 2015 | 10 | 15 | 31 | 42 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
16/10/2015 | 2015 | 10 | 16 | 31 | 42 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
17/10/2015 | 2015 | 10 | 17 | 31 | 42 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
18/10/2015 | 2015 | 10 | 18 | 31 | 43 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
19/10/2015 | 2015 | 10 | 19 | 31 | 43 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
20/10/2015 | 2015 | 10 | 20 | 31 | 43 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
21/10/2015 | 2015 | 10 | 21 | 31 | 43 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
22/10/2015 | 2015 | 10 | 22 | 31 | 43 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
23/10/2015 | 2015 | 10 | 23 | 31 | 43 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
24/10/2015 | 2015 | 10 | 24 | 31 | 43 | 22 | Oct | Q4 | 9 | 6534 | 32 | 198 |
25/10/2015 | 2015 | 10 | 25 | 31 | 44 | 13 | Oct | Q4 | 9 | 3861 | 32 | 117 |
26/10/2015 | 2015 | 10 | 26 | 31 | 44 | 13 | Oct | Q4 | 9 | 3861 | 32 | 117 |
27/10/2015 | 2015 | 10 | 27 | 31 | 44 | 13 | Oct | Q4 | 9 | 3861 | 32 | 117 |
28/10/2015 | 2015 | 10 | 28 | 31 | 44 | 13 | Oct | Q4 | 9 | 3861 | 32 | 117 |
29/10/2015 | 2015 | 10 | 29 | 31 | 44 | 13 | Oct | Q4 | 9 | 3861 | 32 | 117 |
30/10/2015 | 2015 | 10 | 30 | 31 | 44 | 13 | Oct | Q4 | 9 | 3861 | 32 | 117 |
31/10/2015 | 2015 | 10 | 31 | 31 | 44 | 13 | Oct | Q4 | 9 | 3861 | 32 | 117 |
Solved! Go to 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.
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] ) ) )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |