Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

research column to column

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

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        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