Forum Discussion

Power_BI_new's avatar
Power_BI_new
Frequent Visitor
3 years ago

Incorrect Cumulative result on specific rows with Dax

Hi there,

I need your help, I'm stuck, I can't get the 3rd column, I need to create a calculated column which cumulates the amount on certain lines as in the table below :

How do I get my column : "Amount_col" : 
Amount_col = If ('Table'[Category] in {"1.0-Category","2.0-Category","3.0-Category","4.0-Category"},'Table'[Amount],0)
Here are the formulas I tried : 

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Test 1 = SUMX(FILTER('Table',EARLIER('Table'[Category])='Table'[Category]&&EARLIER('Table'[index]) >= 'Table'[index]),'Table'[Amount_col])

index = IF('Table'[Category]="1.0-Category",1,IF('Table'[Category]="2.0-Category",2,IF('Table'[Category]="3.0-Category",3,IF('Table'[Category]="4.0-Category",4,0))))

Result = This gives me double of each value in the column "Amount_col"

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Test 2 =CALCULATE(SUM('Table'[Amount_col]),FILTER(ALL('Table'),'Table'[Amount_col] <= EARLIER('Table'[Amount_col])))

Result = The results I get are totally wrong, for example instead of getting 300,000 for one line, I'm going to get 400,000,000,000,000

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


Thank you in advance for your help

Need Help
Tips and Tricks

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Power_BI_new ,

    Please try:

    Desired Result = 
IF( 
    'Table'[Category] 
        IN {
                "1.0-Category",
                "2.0-Category",
                "3.0-Category",
                "4.0-Category"
            },
    CALCULATE(
        SUM('Table'[Amount_col]),
            FILTER(
                ALL('Table'),
                'Table'[Category] <= EARLIER('Table'[Category])
            )
    ),
    0
)

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

    • ssssss's avatar
      ssssss
      Frequent Visitor
      3 years ago

      Thank you very much for your answer, just that gives me double, I have to click on the calculated column in the fields and put "Average" to get the right amounts.

      How can I modify its directly in the formula?

      • Power_BI_new's avatar
        Power_BI_new
        Frequent Visitor
        3 years ago

        It's not really double, but almost. I have to click average or not sum to get the right amount. The problem is that I have to recover this amount in another column to make an addition, and suddenly I take the figure which is neither summarized nor with an average