Forum Discussion

Jcho10's avatar
Jcho10
Frequent Visitor
9 years ago
Solved

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 woul...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    Jcho10

     

    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

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

Jcho10

 

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

  • Jcho10's avatar
    Jcho10
    Frequent Visitor
    9 years ago

    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