Forum Discussion

RafalK's avatar
RafalK
Advocate IV
10 years ago
Solved

Running Total on a non-date column

Hi guys, I am having some real problems with calculating a running-total in Power BI. Main problem here is that my running total should be based on a calculated measure.   Here is the problem: C...
Need Help
Tips and Tricks
Tutorial Requests
  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    RafalK

     

    Please try with following Calculated Measure for the cumulative sales. It should be able to reduce the calculation.

    Cumulative_Sales_Measure =
CALCULATE (
    SUM ( Table1[Sales] ),
    FILTER ( ALL(Table1), Table1[Sales] >= MAX ( Table1[Sales] ) ),
    VALUES ( Table1[Brand] )
)

    Regards,

     

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
10 years ago

RafalK

 

In this scenario, you can rank the sales for each brand first, and then get the cumulative sales according to the rank. Please create two calculated columns with following formulas.

 

RankInBrand = 
RANKX (
    FILTER ( Table1, EARLIER ( Table1[Brand] ) = Table1[Brand] ),
    Table1[Sales]
)

Cumulative Sales = 
SUMX (
    FILTER (
        Table1,
        Table1[RankInBrand] <= EARLIER ( Table1[RankInBrand] )
            && Table1[Brand] = EARLIER ( Table1[Brand] )
    ),
    Table1[Sales]
)

 

 

 

Regards,

  • RafalK's avatar
    RafalK
    Advocate IV
    10 years ago

    Thanks for the reply..

     

    I got stuck on 

    RANKX (
    FILTER ( Table1, EARLIER ( Table1[Brand] ) = Table1[Brand] ),
    Table1[Sales]
)

    EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

     

    Looking for a workaround...

    • v-sihou-msft's avatar
      v-sihou-msft
      Microsoft Employee
      10 years ago

      RafalK

       

      Please make sure you’re creating a Calculated Column instead of a Measure with above formulas.

       

      Regards,

      • RafalK's avatar
        RafalK
        Advocate IV
        10 years ago

        Thanks Simon,

        This solution might work in theory, but the calculation eats all of my RAM (13 GB) and drops due to lack of space.

        Perhaps Power BI just can't handle those calculations. I would probably have to push that to a Calculated Measure and this may be impossible.

         

        Such a simple task, yet so hard to implement.

        Thanks for the help.

  • vin26's avatar
    vin26
    Resolver I
    6 years ago

    Thanks a lot for the solution, I was struggling to get the cumulative total