cancel
Showing results for
Did you mean:

## 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):

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

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

Regards,

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

Regards,

## Re: Running Total on a non-date column

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

## 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,

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

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

Regards,

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

New Member

## Re: Running Total on a non-date column

Hi Guys this does  not seem to work .

Product NameProduct Sales

 Product 63 25145 Product 64 25413 Product 65 25681 Product 66 25949 Product 67 26217 Product 68 26485 Product 69 26753 Product 70 27021 Product 71 27289 Product 72 27557 Product 73 27825 Product 74 28093 Product 75 28361 Product 76 28629 Product 77 28897 Product 78 29165 Product 79 29433

Cumulative_Sales_Measure =

CALCULATE (

SUM ( Product[Total Sales] ),

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

VALUES ( Product[Product Name )

)

Announcements

#### 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!

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

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

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

Top Solution Authors
Top Kudoed Authors