Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Two tables - Lookup

Hi - I have two tables. Sales and Return tables that show sales and return amt for different products. I want to show (or chart) in a table, the total sales and return cost for each item. When I do that, the sales amt is shown correctly but the return amt is shown as one single figure. I don't have any relationship defined between these two tables. 

 

How do I do this correctly? 

 

 

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

    Hi Anonymous,

     

    Create a summary table based on Product Return table.

    Summary Product Return =
SUMMARIZE (
    'Product Return',
    'Product Return'[Product],
    "Return Total", SUM ( 'Product Return'[Return] )
)

    In Product Sales table, add a new calculated column using LookUpValue.

    return =
LOOKUPVALUE (
    'Summary Product Return'[Return Total],
    'Summary Product Return'[Product], 'Product Sales'[Product]
)

    Then, add columns [Product], [Sales] and [return] from 'Product Sales'  into table visual. Please note that when you add [return] into table visual, you should choose  'Don't summarize ' option.

     

    If you need to show total values for this column, that is to say, you want it to be summed. Please use this formula instead:

    return2 =
LOOKUPVALUE (
    'Summary Product Return'[Return Total],
    'Summary Product Return'[Product], 'Product Sales'[Product]
)
    / CALCULATE (
        COUNT ( 'Product Sales'[Product] ),
        ALLEXCEPT ( 'Product Sales', 'Product Sales'[Product] )
    )

    You can see the difference between above two formulas.

     

    Best regards,
    Yuliana Gu

3 Replies

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

    Hi Anonymous,

     

    Create a summary table based on Product Return table.

    Summary Product Return =
SUMMARIZE (
    'Product Return',
    'Product Return'[Product],
    "Return Total", SUM ( 'Product Return'[Return] )
)

    In Product Sales table, add a new calculated column using LookUpValue.

    return =
LOOKUPVALUE (
    'Summary Product Return'[Return Total],
    'Summary Product Return'[Product], 'Product Sales'[Product]
)

    Then, add columns [Product], [Sales] and [return] from 'Product Sales'  into table visual. Please note that when you add [return] into table visual, you should choose  'Don't summarize ' option.

     

    If you need to show total values for this column, that is to say, you want it to be summed. Please use this formula instead:

    return2 =
LOOKUPVALUE (
    'Summary Product Return'[Return Total],
    'Summary Product Return'[Product], 'Product Sales'[Product]
)
    / CALCULATE (
        COUNT ( 'Product Sales'[Product] ),
        ALLEXCEPT ( 'Product Sales', 'Product Sales'[Product] )
    )

    You can see the difference between above two formulas.

     

    Best regards,
    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks Yuliana !!

  • petrcz's avatar
    petrcz
    Regular Visitor
    8 years ago

    Hi, check this

    https://msdn.microsoft.com/en-us/library/gg492170.aspx and this https://msdn.microsoft.com/en-us/library/ee634551.aspx

    Use Earlier function to lookup a value for any single row...

    The output in your case should be

     

    1. Add a new column in "Sales" table, you can give it a name "Return"

    2. Insert this formula in "Sales" table", column "Return"

    LOOKUPVALUE('Table in whis the return is'[Return],'Table in which the return is'[Product],earlier[Product])