Forum Discussion

OKgo's avatar
OKgo
Icon for Helper IV rankHelper IV
8 years ago
Solved

Cummulative sum visible in edit queries

Within PowerBi is there a way to have a cumulative total visible in the edit queries screen?

 

This is how I would tackle this in Excel. Sort by "unique' then by "date", simple IF formula:

thank you in advance!

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi OKgo,

     

    To get a cumulative total column, please try:

    Running total Col =
CALCULATE (
    SUM ( 'Table1'[Value] ),
    FILTER (
        ALLEXCEPT ( 'Table1', 'Table1'[unique] ),
        Table1[Date] <= EARLIER ( Table1[Date] )
    )
)

     

    To create a running total measure, please refer to:

    Running total measure =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[unique] ),
        Table1[Date] <= MAX ( Table1[Date] )
    )
)

     

    Best regards,

    Yuliana Gu

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi OKgo,

     

    To get a cumulative total column, please try:

    Running total Col =
CALCULATE (
    SUM ( 'Table1'[Value] ),
    FILTER (
        ALLEXCEPT ( 'Table1', 'Table1'[unique] ),
        Table1[Date] <= EARLIER ( Table1[Date] )
    )
)

     

    To create a running total measure, please refer to:

    Running total measure =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[unique] ),
        Table1[Date] <= MAX ( Table1[Date] )
    )
)

     

    Best regards,

    Yuliana Gu

    • OKgo's avatar
      OKgo
      Icon for Helper IV rankHelper IV
      8 years ago

      Dear Yuliana, thank you so much! That was exactly the breakthough I needed. I added the column in the modelling tab and the graphs work great. Can you please explain how you got your first screenshot? It looks like a preview from the edit queries screen which looks great.

  • OKgo's avatar
    OKgo
    Icon for Helper IV rankHelper IV
    8 years ago

    My best atempt using

    https://www.daxpatterns.com/cumulative-total/

     

    However is not working. Not sure how to introduce the filter for unique....

     

    Cumulative Quantity = 
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )

     


    )