DAX query to add numbers until a condition is met

[Anonymous]

I have two columns named 'HA' and 'PA'. I want to add values in column 'HA' once it is greater than or equal to the value in 'PA'.

HA       PA

1.58     3.24

3.24     3.24

3.23     3.24

the values in column 'PA' will be the same. I want to add values in column 'HA' once the sum is greater than or equal to the value in column 'PA' For eg: it should add the first two rows (1.58 + 3.24 = 4.82) in column 'HA', as its sum will be greater than or equal to the value in columns 'PA'. It should not add the 3rd value in column 'HA'.

[v-yuta-msft · Accepted solution]

@Anonymous ，

Firstly, click query editor-> add columns-> add index column, then create a calculate column using DAX below:

Running Total = CALCULATE(SUM('Table'[HA]), FILTER('Table', 'Table'[Index] <= EARLIER('Table'[Index])))

 

Then, create a measure as below:

Result = 
VAR Index = CALCULATE(MAX('Table'[Index]), FILTER('Table', 'Table'[Running Total] = MIN('Table'[Running Total]) && 'Table'[Running Total] >= 'Table'[PA])) + 1
RETURN
CALCULATE(SUM('Table'[HA]), FILTER('Table', 'Table'[Index] <= Index))

 

Community Support Team _ Jimmy Tao

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

[Anonymous]

@v-yuta-msft , Thank you for your help. 
Is there a way to create a Calculated column in place of this measure 'Result'?

---

@v-yuta-msft wrote:

@Anonymous ，

Firstly, click query editor-> add columns-> add index column, then create a calculate column using DAX below:

Running Total = CALCULATE(SUM('Table'[HA]), FILTER('Table', 'Table'[Index] <= EARLIER('Table'[Index])))

 

Then, create a measure as below:

Result = 
VAR Index = CALCULATE(MAX('Table'[Index]), FILTER('Table', 'Table'[Running Total] = MIN('Table'[Running Total]) && 'Table'[Running Total] >= 'Table'[PA])) + 1
RETURN
CALCULATE(SUM('Table'[HA]), FILTER('Table', 'Table'[Index] <= Index))

 

Community Support Team _ Jimmy Tao

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

---

[Anonymous]

Hi

are those columns of a table or measures?

Also, how do you choose values to sum? Are they sorted in some ways? 

I guess finally you want to have a MEASURE with the summed amount rather than a column?

In general, DAX cannot really work "iteratively" (i.e. do this until a condition is met) but you can achieve the same result in different ways. For example you could create a calculated column with the sum of the previous values (you'll need to RANK them in some ways, because DAX does not have the concept of "previous row") and then choose with a FILTER the row where the new value is higher than the value in PA.