Forum Discussion

Helper II

7 years ago

Solved

Obtain single value from column with max values into a new column

Easy question: how to get just a single value from sumQty_by_item column? location part qty sum_qty_by_item date expected result 1 111 10 60 01.05.2019 60 2 111 20 60 0...

Need Help

v-cherch-msft

7 years ago

Hi mhsk

I would suggest you to add an index column in query editor.Then create the column as below:

Column =
VAR pre_sum =
    CALCULATE (
        MAX ( 'Table'[sum] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
RETURN
    IF (
        pre_sum <> 'Table'[sum],
        CALCULATE ( MAX ( 'Table'[sum] ), ALLEXCEPT ( 'Table', 'Table'[sum] ) ),
        0
    )

Regards,

mhsk

Helper II

7 years ago

ok this works while parts are always represented in multiple rows, but there are also cases where part can have just one row (no index-1 possible) - like part 555 below, could you have a look ?