Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

retrieve column value from current iterated row

Hello all,   I would like to create a measure that sums a column based on a filter I apply. The query gets complex beacuse I need to retrieve the column value from the current iterated row. My cont...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Hi handrade,

     

    Please follow steps below:

     

    1. Click Edit Queries-> Merge Queries as New to merge 'Production' and 'Shipping' into one table based off Part Number.

     

    2. Click Add Column-> Index Column to create a index column so that the iteration can be implemented by Index Column.

    3. After Close&Apply, create a calculate column running total to achieve the cummulative total of Requested Quantity, then you can achieve the Forecast.

    Running Total = 
CALCULATE (
    SUM ( Merge1[Requested Quantity] ),
    FILTER (
        Merge1,
        Merge1[Index] <= EARLIER ( Merge1[Index] )
            && Merge1[Part Number] = EARLIER ( Merge1[Part Number] )
    )
)

    Forecast = MAX(Merge1[Production Quantity]) - MAX(Merge1[Running Total])
     
     
    Regards,
    Jimmy Tao

     

v-yuta-msft's avatar
v-yuta-msft
Community Support
7 years ago

Hi handrade,

 

Please follow steps below:

 

1. Click Edit Queries-> Merge Queries as New to merge 'Production' and 'Shipping' into one table based off Part Number.

 

2. Click Add Column-> Index Column to create a index column so that the iteration can be implemented by Index Column.

3. After Close&Apply, create a calculate column running total to achieve the cummulative total of Requested Quantity, then you can achieve the Forecast.

Running Total = 
CALCULATE (
    SUM ( Merge1[Requested Quantity] ),
    FILTER (
        Merge1,
        Merge1[Index] <= EARLIER ( Merge1[Index] )
            && Merge1[Part Number] = EARLIER ( Merge1[Part Number] )
    )
)

Forecast = MAX(Merge1[Production Quantity]) - MAX(Merge1[Running Total])
 
 
Regards,
Jimmy Tao