Two tables - Lookup

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