WHAT TO DO FOR THIS FORMULA IS NOT CALCULATING CORRECT?

[samgamer3000]

IM TRYING TO USE THIS FORMULA TO CALCULATE MY CUMULATIVE TOTAL BUT FOR THE VALUES WHERE IT IS ZERO IT IS RESETTING AND BRINGING THE TOTAL VALUE WHICH IS WRONG WHAT SHOULD I DO?

RUNNINGTOTALCUM = CALCULATE([ExcessStock2],WINDOW(1,ABS,0,REL,ALLSELECTED(Dim_old_Code[OLD_CODE]),ORDERBY([ExcessStock2],DESC)))

IF YOU SEE MY TOTAL IS 665400 BUT ITS SHOWING, MY RESULT IS 0. IT SHOULD START FROM 390797 AND IF THE NEXT ITEM AND 0 IT SHOULD BE 390797 INSTEAD OF 665400 AND THE NEXT IT CALCULATES CORRECT 390797+115000 = 505797.

WHATS THE PROBLEM AND WHAT SHOULD I DO?

[Anonymous]

Hi @samgamer3000 ,
Sample data

You can ttry this mesure

Running total = 
CALCULATE(
    SUMX(
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Name] <= MAX('Table'[Name])
        ),
        [ExcessStock2]
    )
)

Fianl output

In the above example, the name column is used as a sequential column for explicit ordering, if you don't have explicitly ordered columns in your data, you'll need to create them first and then use them to calculate.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[sss12231]

Hi it gives me this issue. Is there any alternative to calcualte running total if you data set is long?

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Show the expected result there.

[tharunkumarRTK]

@samgamer3000 

The reason why it is showing total value when your [ExpressStock2] is 0 is 

in your orderby parameter you have put [ExpressStock2] desc this is the sort order which it uses to perform the running sum, Ideally in descending order 0 comes after all positive numbers, so if I assume you only have positive numbers then 0 value would be in the last record before total. and (Last but one value) + 0 = (Last but one value) ~ 665400

Just put a descending sort order on your Expressstock2 measure in your table visual 
you will clearly understand what is happening.

and also correct the formula with the correct index column.

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[ahadkarimi]

Hi @samgamer3000, try this code, and if you encounter any issues, let me know.

RUNNINGTOTALCUM =
VAR PreviousTotal = 
    CALCULATE(
        MAX([ExcessStock2]),
        FILTER(
            ALLSELECTED(Dim_old_Code[OLD_CODE]),
            Dim_old_Code[OLD_CODE] < MAX(Dim_old_Code[OLD_CODE])
        )
    )
RETURN
IF(
    [ExcessStock2] = 0,
    PreviousTotal,
    PreviousTotal + [ExcessStock2]
)

Did I answer your question?  If so, please mark my post as the solution!
Your Kudos are much appreciated!  Proud to be a Resolver III !

[sss12231]

The MAX function only accepts a column reference as the argument number 1. It is giving me this errors. 

[ahadkarimi]

give this a try, and if you encounter any issues, let me know.

RUNNINGTOTALCUM =
VAR PreviousRunningTotal =
    CALCULATE(
        SUM([ExcessStock2]),
        FILTER(
            ALLSELECTED(Dim_old_Code[OLD_CODE]),
            Dim_old_Code[OLD_CODE] <= MAX(Dim_old_Code[OLD_CODE])
        )
    )
RETURN
IF(
    [ExcessStock2] = 0,
    PreviousRunningTotal - [ExcessStock2],
    PreviousRunningTotal
)

Did I answer your question?  If so, please mark my post as the solution!
Your Kudos are much appreciated!  Proud to be a Resolver III !

[sss12231]

But the problem is my excesstock2 is a measure so when I try to add in the previous running total part is shows error but in second part it is accepting.