Forum Discussion

Torbuzz's avatar
Torbuzz
Frequent Visitor
2 years ago
Solved

Accumulative function and sum not working

Hi All,

Below is my data sample

I want to have result like this 

I use this formula that the accumulation didn't work

SisaStok =
Var TotInItem = SUM('Item'[In])
Var TotOutItem = SUM('Item'[Out])
Var TotAll = TotInItem - TotOutItem
Var Result = CALCULATE(IF(TotAll<0,0,TotAll),FILTER(ALLSELECTED(Kalender),Kalender[Date]<=MAX(Kalender[Date])))
RETURN
Result

 

Any support on this, highly appriciated.

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Torbuzz ,

     

    If you want to get result as your question, I think you need to sort your table by [Date] and [Name].

    Measure:

    GrandTotal = CALCULATE(SUM('Table'[In]) - SUM('Table'[Out]))
    SisaStok =
VAR _Step1 =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[Index],
        Kalender[Year],
        Kalender[MonthSort],
        'Table'[Name],
        "GrandTotal", [GrandTotal],
        "Group",
            MAXX (
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    [GrandTotal] < 0
                        && 'Table'[Index] < EARLIER ( 'Table'[Index] )
                ),
                'Table'[Index]
            )
    )
VAR _Step2 =
    ADDCOLUMNS (
        _Step1,
        "RunningTotal",
            IF (
                [GrandTotal] < 0,
                0,
                SUMX (
                    FILTER (
                        _Step1,
                        [Group] = EARLIER ( [Group] )
                            && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
                    ),
                    [GrandTotal]
                )
            )
    )
RETURN
    SUMX ( FILTER ( _Step2, [Index] IN VALUES ( 'Table'[Index] ) ), [RunningTotal] )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

3 Replies

  • Torbuzz's avatar
    Torbuzz
    Frequent Visitor
    2 years ago

    Now I change the formula to be like this :

    SisaStok =
    Var Result = CALCULATE([GrandTotal],FILTER(ALLSELECTED(Kalender),Kalender[Date]<=MAX(Kalender[Date])))
    RETURN
    IF(Result<0,0,Result)
     
    The result of the February speaker item is not 20 and the sum value should be 200

    What should I do? 

     
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Torbuzz ,

       

      If you want to get result as your question, I think you need to sort your table by [Date] and [Name].

      Measure:

      GrandTotal = CALCULATE(SUM('Table'[In]) - SUM('Table'[Out]))
      SisaStok =
VAR _Step1 =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[Index],
        Kalender[Year],
        Kalender[MonthSort],
        'Table'[Name],
        "GrandTotal", [GrandTotal],
        "Group",
            MAXX (
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    [GrandTotal] < 0
                        && 'Table'[Index] < EARLIER ( 'Table'[Index] )
                ),
                'Table'[Index]
            )
    )
VAR _Step2 =
    ADDCOLUMNS (
        _Step1,
        "RunningTotal",
            IF (
                [GrandTotal] < 0,
                0,
                SUMX (
                    FILTER (
                        _Step1,
                        [Group] = EARLIER ( [Group] )
                            && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
                    ),
                    [GrandTotal]
                )
            )
    )
RETURN
    SUMX ( FILTER ( _Step2, [Index] IN VALUES ( 'Table'[Index] ) ), [RunningTotal] )

      Result is as below.

       

      Best Regards,
      Rico Zhou

       

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

       

      • Torbuzz's avatar
        Torbuzz
        Frequent Visitor
        2 years ago

        Hi Anonymous ,

        Perfect, many thanks.