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: t...
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

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.