topic Re: Calculate the sum of multiple row with a condition in DAX Commands and Tips

Calculate the sum of multiple row with a condition

tamim — Fri, 01 May 2020 16:54:35 GMT

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.

Re: Calculate the sum of multiple row with a condition

camargos88 — Fri, 01 May 2020 17:52:41 GMT

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

Re: Calculate the sum of multiple row with a condition

tamim — Sat, 02 May 2020 11:24:24 GMT

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]))

)

Re: Calculate the sum of multiple row with a condition

camargos88 — Sat, 02 May 2020 14:10:01 GMT

tamim ,

Can you post the error ?

Ricardo

Re: Calculate the sum of multiple row with a condition

Anonymous — Sat, 02 May 2020 14:46:22 GMT

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

Re: Calculate the sum of multiple row with a condition

tamim — Tue, 05 May 2020 14:47:26 GMT

it look similar to this in Excel