Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RafalK
Advocate IV
Advocate IV

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

@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

10 REPLIES 10
Anonymous
Not applicable

I have same query no body going to resolve this problem even there is no toturial for this concern.

 

NameRetailerChannelValContriFlag Town Level
AchampetR2C14236%Top 80%
AchampetR1C23026%Top 80%
AchampetR3C12622%Top 80%
AchampetR4C21916%Rest
ADDANKIR7C14538%Top 80%
ADDANKIR8C22622%Top 80%
ADDANKIR5C12118%Top 80%
ADDANKIR9C21311%Rest
ADDANKIR6C11210%Rest
ADILABADR11C27535%Top 80%
ADILABADR10C14421%Top 80%
ADILABADR13C23315%Top 80%
ADILABADR14C13115%Top 80%
ADILABADR12C23014%Rest
Anonymous
Not applicable

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 )

)

v-sihou-msft
Employee
Employee

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

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

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

@RafalK

 

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

 

Regards,

S.noJob NameFail_countRunning_fail_count_Sum
    
1sapbw110110
2Sslog90200
3tdnxt63263
4rtxls22285

 How do i get the  S.No generated based on   Fail_count ?  and  running_count  as shown in the sample. .. Job_name and Fail_count is available.

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

 

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

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.