Forum Discussion

pceglie's avatar
pceglie
Resolver I
9 years ago
Solved

POWERBI Desktop - Running total per column

Hi, I have the following table

 

The desired result is the total for the selected month containing the sum of all previous columns (per customer)

For example if I select Feb (in a filter in my report) the result must be

 

If I select Mar the result must be

and so on

 

Thanks,

 

Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    Hi pceglie,

     

    I have tested it by using the sample data below(Table name:Table1).


    And then create a new table by using the DAX below.
    Table = SUMMARIZE(Table1,Table1[MonthName],Table1[Customer],"Monthtotal",CALCULATE(SUM(Table1[Rev]),ALLEXCEPT(Table1,Table1[MonthName],Table1[Customer])))

    Add a running total column by using the DAX below.
    RunningTotal = CALCULATE(SUM('Table'[Monthtotal]),FILTER(ALLEXCEPT('Table','Table'[Customer]),'Table'[MonthNumber]<=EARLIER('Table'[MonthNumber])))

     

    Then use a martix visual to display the data like below.

    Regards,

    Charlie Liao

     

4 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    9 years ago

    When you load the data into power bi, you should unpivot it first. Your table will then look like this

     

    customer, month, value

     

     

    From there you can place customer and month as you need. Do you know how to do this?