Calculate the sum of multiple row with a condition

[tamim]

Hi,

I have the following table, as you can see the Sum of distanceKM is 512.

I want o make a measure to calculate the Sum of distanceKM until its value is less than 450km.

So, I want a condition in the measure to stop calculating the Sum once it will reach 450km.

The reason is that I want to know on which row the sum will be 450km or less.

 

[Anonymous]

To do this you have to have an order among all the distances. If you use only the distances themselves without any outer order, then you can calculate a cumulative distance in 2 ways:
1. from the smallest distance to the biggest or
2. the other way round.
You have not specified the order, so this task does not have a clear solution.

Best
D

[tamim]

it look similar to this in Excel 

[camargos88]

Hi @tamim ,

Try this code for measure:

Measure = CALCULATE(

IF(SUM('Table'[Column1]) <= 10; SUM('Table'[Column1]); BLANK());

FILTER(ALL('Table'); 'Table'[Column1] <= MAX('Table'[Column1]))

)

Did I answer your question? Mark my post as a solution!
Ricardo

[tamim]

Hi, Thank you for your answer

But I am still gettin an error

check the underlined:

Measure = CALCULATE(

IF(SUM(sheet2[distanceKM]) <= 10, SUM(sheet2[distanceKM]), BLANK()),

FILTER(ALL(sheet2), sheet2[distanceKM] <= MAX(sheet2[distanceKM]))

)

[camargos88]

@tamim ,

Can you post the error ?

Ricardo