cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RafalK Advocate II
Advocate II

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:

Calculate a column, that will show, if your product is in the product group, that makes 80% of sales per Brand. 

Someting like this (the problem is - how to calculate column E):

 

1.PNG

 

 

 

 

 

 

 

 

 

 

 

I have tried several tutorials and forums but still no answer. 

Looks like the support bot running functions is very limited in Power BI.

 

Thanks

Rafał Kun
1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Running Total on a non-date column

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

1.png

Regards,

 

View solution in original post

7 REPLIES 7
Moderator v-sihou-msft
Moderator

Re: Running Total on a non-date column

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

 

 

46.png

 

Regards,

RafalK Advocate II
Advocate II

Re: Running Total on a non-date column

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

Rafał Kun
Moderator v-sihou-msft
Moderator

Re: Running Total on a non-date column

@RafalK

 

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

 

Regards,

RafalK Advocate II
Advocate II

Re: Running Total on a non-date column

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.

Rafał Kun
Moderator v-sihou-msft
Moderator

Re: Running Total on a non-date column

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

1.png

Regards,

 

View solution in original post

metxtli
Regular Visitor

Re: Running Total on a non-date column

 

Hi!

 

Did you found a solution to the memory problem? I have the same problem, we are trying to do the same exercise you put on the table.

 

Regards

JCarlos

akrishnan
New Member

Re: Running Total on a non-date column

Hi Guys this does  not seem to work .

Product NameProduct Sales 

Product 6325145
Product 6425413
Product 6525681
Product 6625949
Product 6726217
Product 6826485
Product 6926753
Product 7027021
Product 7127289
Product 7227557
Product 7327825
Product 7428093
Product 7528361
Product 7628629
Product 7728897
Product 7829165
Product 7929433

Cumulative_Sales_Measure =

CALCULATE (

    SUM ( Product[Total Sales] ),

    FILTER ( ALL(Product), Product[Total Sales] >= MAX ( Product[Total Sales] ) ),

    VALUES ( Product[Product Name )

)

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors