Forum Discussion

ElliotP's avatar
ElliotP
Post Prodigy
10 years ago
Solved

Cumulative Total

Hi,   I'm at an absolute loss as to how to calculate a cumulative total. I've tried googling, reading the forums, following the documentation, decomposing the calculation, trying it as both a measu...
Need Help
Show and Tell
  • Sean's avatar
    Sean
    10 years ago

    ElliotP Sorry about the original post. It was from my phone and had typos :smileywink:

     

    Okay here is the formula for Running Total as a Calculated Column (prorerly formatted)

     

    Running Total COLUMN =
CALCULATE (
    SUM ( 'All Web Site Data (2)'[UniquePageviews] ),
    ALL ( 'All Web Site Data (2)' ),
    'All Web Site Data (2)'[Date] <= EARLIER ( 'All Web Site Data (2)'[Date] )
)

     

    And as you can see it works! :smileyhappy:

     

     

    And here's the MEASURE formula

     

    Running Total MEASURE = 
CALCULATE (
    SUM ( 'All Web Site Data (2)'[UniquePageviews] ),
    FILTER (
        ALL ( 'All Web Site Data (2)' ),
        'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] )
    )
)

     

    Which also works...

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

What is the workaround for it then?

 

Cumulative Scheduled Quantity = CALCULATE(SUM(F_PROJECT_PROGRESS_WORKMEN[Scheduled_QTY]),FILTER(ALL(D_DATE[Date_Key]),D_DATE[Date_Key]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1) && D_DATE[Date_Key]<=[Today's Date]))

 

Cumulative Actual Quantity = CALCULATE(SUM(F_PROJECT_PROGRESS_WORKMEN[Actual_QTY]),FILTER(ALL(D_DATE[Date_Key]),D_DATE[Date_Key]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1) && D_DATE[Date_Key]<=[Today's Date]))

 

These are the measures I created as I can't create Columns in Direct query mode. I didn't get the error you got though, unless I am misinformed on something here.

Rajiv1237's avatar
Rajiv1237
Resolver I
9 years ago

1. Its Direct Query Mode (Live connection with SQL) not loaded the data in Power BI

2. Filter cannot be used in Direct Query Mode.

3. I have written this formula for measure not for column

 

 

My Question is:

I can't find a way to calculate a running total, without using FILTER. Filter is not supported in PowerBi direct query-mode.

All help is appreciated

 

Not supported in Direct Query Mode Smiley Sad

 

Running Total in DAX = CALCULATE( SUM('Table'[QTY]),
    FILTER(
        ALLSelected('Table'),
        'Table'[Date] <= MAX('Table'[Date])
    )
)