Find a value on another table based on several criteria

[Raph]

Hello,

Let's say I have a Matrix with customers splitted by countries on rows and their sales amounts on values.

I have a table in my model with all the discount percentages by customers and by countries (a customer will have different % depending the countries the products) :

Customer    Country   % disc product A      % discount product B

A                  US                    12%                              14%

A                  FR                       9%                              12%

B                  UK                    13%                               11%

B                  FR                     12%                               13%

C                  FR                     10%                               9%

Which DAX function should I use to create a measure which will return the good discount %? For exemple, on the line of the matrix where we see the sales of product A to Customer B in UK, I would like to have a new column "Discount amount" with 13% multiplied by the total sales of product A to Customer B in UK.

I hope this request is clear!

Thank you very much for your help.

Best regards,

Raph

[v-chuncz-msft · Accepted solution]

@Raph,

You may select Unpivot Columns in Query Editor first, then use LOOKUPVALUE in DAX.