Cummulative sum visible in edit queries

[OKgo]

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!

[v-yulgu-msft · Accepted solution]

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]

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]

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] )
    )

)