research column to column

[Anonymous]

Hello everyone,

I would like to calculate the quantity we ve sold for each item we have (on the last 12 months).

But some reference has changed.

So, if there is no old item ref, I just would like the sum that I made.

And if there is an old item no, I would calculate the quantity of the item no + the quantity of the old item no

Item n°   Old item n°   Qty

X12            TU                2

51            VE                1

TU1            /                  1

VE            /                  1

Exepected results:

X12 => 2

TU1 => 3

51 => 1

VE => 2

[v-yulgu-msft]

Hi @Anonymous,

Please add a calculated column:

Sum Qty = Tab_1[Qty]+LOOKUPVALUE(Tab_1[Qty],Tab_1[Old item n],Tab_1[Item n])

Regards,

Yuliana Gu

[Anonymous]

Sorry,

This is not so simple. LOOKUPVALUE doesn't work.

My exemple is too simple. Item n° is not a primary key (not a UNIQUE)

This is closer than the reality :

Item n°   Old item n°   Qty

X12            TU1                2

51            VE                1

TU1            /                  1

TU1          /                   2

VE            /                  1

51             VE               2

Expected results :

X12 => 2

51 => 4

TU1 => 3

VE => 1

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Item n°   Old item n°   Qty

X12            TU1                2

51            VE                1

TU1            /                  1

TU1          /                   2

VE            /                  1

51             VE               2

You can new a calculated table.

Tab_2 =
VAR temptab1 =
    SUMMARIZE ( Tab_1, Tab_1[Old item n], "Qty1", SUM ( Tab_1[Qty] ) )
RETURN
    SUMMARIZE (
        Tab_1,
        Tab_1[Item n],
        "Total Qty", SUM ( Tab_1[Qty] )
            + SUMX ( FILTER ( temptab1, [Old item n] = EARLIER ( Tab_1[Item n] ) ), [Qty1] )
    )

Best regards,

Yuliana Gu