How to search for similar cells in one column, but use the numbers from another column

[Rose__123]

I have a table containing invoice numbers, customer IDs, total cost, salespersons, etc. and I would like to calculate the commission that should be provided to the salesperson. The commission percentage is set to 5 %, however, for some specific customers, the percentage is different. The table looks something like this:

Invoice number	CustomerID	Cost	Salesperson	Commission
1	Berit	1005.00	Lars	?
2	10002324	304.00	Susan	?
3	Hello	10.00	Susan	?
4	13432	20000.89	Lars	?

Therefore, I also have a lookup table that contains specific customer names or numbers and the associated commission.  It looks like this:

CustomerID	Commission percentage
Berit	6
10002653	9

I would then like to write the code to calculate the commission that should be provided to the salespersons for the different invoices. 

I can find if the invoice contains a customer that provided a different commission, but I can't apply the commission.  How should I do?

IF( Invoices[CustomerID] IN DISTINCT(Commission_exceptions[CustumerID]),

 ???,

Invoices[Cost]/100*5 )

[selimovd · Accepted solution]

Hey @Rose__123 ,

if you want the comission as a calculated column in the Invoice table try the following calculated column:

Comission =
VAR vRowCustomer = Invoice[CustomerID]
RETURN
    CALCULATE(
        MAX( Comission[Commission percentage] ),
        Comission[CustomerID] = vRowCustomer
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Rose__123]

Thank you it works! 🙏