Forum Discussion
Cumulative Sum
Hi everybody,
I'm learning PBI and I would like to create a matrix table to calculate :
-In a first column : the total turnover by month
-In a second column : the cumulative turnover
I would like to get this result :
| MONTH | TURNOVER | CUMULATIVE TURNOVER |
| 1 | 84 693 | 84 693 |
| 2 | 190 685 | 275 378 |
| 3 | 60 935 | 336 313 |
| 4 | 119 286 | 455 599 |
| 5 | 44 570 | 500 169 |
| 6 | 47 644 | 547 813 |
| 7 | 130 880 | 678 693 |
| 8 | 59 472 | 738 165 |
| 9 | 48 321 | 786 486 |
| 10 | 52 564 | 839 050 |
| 11 | 16 312 | 855 362 |
| 12 | 57 059 | 912 421 |
I've created a measure TURNOVER which works well :
TURNOVER =
VAR
DebutCA = SUMMARIZE('Parametres comptes';'Parametres comptes'[DebutCA])
VAR
FinCA = SUMMARIZE('Parametres comptes';'Parametres comptes'[FinCA])
VAR
JournalVentes = SUMMARIZE('Parametres journaux';'Parametres journaux'[JournalVentes])
RETURN
CALCULATE(sum('Journaux 3 ans'[Solde]);
'Journaux 3 ans'[Compte]>=DebutCA;
'Journaux 3 ans'[Compte]<=FinCA;
'Journaux 3 ans'[Journal]=JournalVentes;
)
I would like to create a measure to calculate cumulative turnover.
I don't know how to proceed.
I tried differents measures but it doesn't works...
Thank you for your help.
Sincerely
In this scenario, you need to create a Calendar table first with following formula. And create relationship between the Calendar table with the fact table with Date key.
Calendar = CALENDAR ( "1/1/2015", "12/31/2016" )
Since I don’t know your table structure, I just create a simple fact table as below. The Measure expression for TURNOVER is “TURNOVER = SUM( Table1[Sold] )”.
Then we can create a Measure with following formula to get the cumulative turnover.
CUMULATIVE TURNOVER = CALCULATE ( [TURNOVER], FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) )You can take a look at this document to understand more about Cumulative Sum.
I've also uploaded my .pbix file here for reference.
Best Regards,
Herbert
2 Replies
In this scenario, you need to create a Calendar table first with following formula. And create relationship between the Calendar table with the fact table with Date key.
Calendar = CALENDAR ( "1/1/2015", "12/31/2016" )
Since I don’t know your table structure, I just create a simple fact table as below. The Measure expression for TURNOVER is “TURNOVER = SUM( Table1[Sold] )”.
Then we can create a Measure with following formula to get the cumulative turnover.
CUMULATIVE TURNOVER = CALCULATE ( [TURNOVER], FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) )You can take a look at this document to understand more about Cumulative Sum.
I've also uploaded my .pbix file here for reference.
Best Regards,
Herbert
Hi Herbert Liu,
Thank you, it works better with a calendar table !!
I have a second question.
I would like to create 2 tables to compare turnover by month in differents years.
I've created in my calendar date a column with Text Month Name and this measure :
Turnover by month "TURNOVER = SUM( Table1[Sold] "
This table works well
But I don't know how to create the 2nde table to have "Turnover cumulate by month"
Because running total with text month doesn't works...
Do you have an idea ?
Thank very much for helping me
Sincerely
