Wrong amount when cumulating the column

[ssssss]

Hello, 

My objective is to obtain the 3rd column in cumulate, I use the following formula, but I do not obtain the good results in the 1st table, but in the 2nd if by going to the fields and putting average instead of sum .

I don't understand what is happening, because I would like to add the column: Amount+result (obtain the 353...330... and not the 700..)

In the Data view I have the correct figures but not in my table in the power bi report view

Result = IF( 'Table'[Category] IN {"1.0- Category ","2.0- Category ","3.0- Category ","4.0- Category"},CALCULATE(SUM('Table'[Column to cumulate]),FILTER(ALL('Table'),

'Table'[Category]<= EARLIER('Table'[Category]))),0)

Table whose Result column amounts is false :

Table whose Result column amounts is true ( I use the same calculated column except that I go to the fields and I put average instead of sum ). I would like to get this result directly from the Result formula, I don't understand what is happening 

Thanks in advance for the help

[Anonymous]

Hi @ssssss ,

I used your DAX formula to create a calculated column, the field is in sum format, and still shows the correct result.

You can also create a measure.

Measure = SUM('Table'[Column to cumulate])

Then you can create a calculated column. Please note the spaces in the Category field in the formula.

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

Then the result is as follows.

Or you can also try the sumx function for row-by-row aggregation.

SUMX function (DAX) - DAX | Microsoft Learn

Best Regards,

Neeko Tang

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

[ssssss]

Hello,

First of all thank you very much for your answer. But the problem is that in this table I have other columns which I feel change the amount.

I only get the correct amounts by going here (see image below) and clicking average, if I don't my numbers are wrong (just over double). Knowing that the numbers I get are not the average of the base number displayed.

I also did the following test:

Test  =SUMMARIZE('Table','Table''[Category],'Table''[Result],'Table''[Amount])

It works perfectly I get the right results, but when I add another column, it shows me the false results that I have in my initial database.

How to make so that in my initial base, I directly obtain the good amounts, without having to click on average to obtain them. I don't know how the other columns I have in the database manage to change my numbers

Thanks in advance !

[Anonymous · Accepted solution]

Hi @ssssss ,

You can try to create a measure.

Measure =

SUMX(

    SUMMARIZE(

        'Table',

        'Table'[Category],

        'Table'[Amount],

        "total",[Result]

    ),

    [total]

)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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