different calculation depending on ID

[anonymous188]

Hi,

I have the following made-up tables:

Employee ID	Product Sold
111	Banana
112	Apple
112	Kiwi
113	Apple
113	Kiwi
113	Mango

Employee ID	Role ID
111	1
112	1
113	2
114	1
115	2

I'm trying to count the number of distinct products each employee has sold, but then multiply the number sold by 3 if their Role ID is 2 and by 1 if their Role ID is 1.  I'd like to create a chart within Power Bi with this information which would look like the following:

Employee ID	Bonus
111	1
112	2
113	9
114	0
115	0

Thanks in advance for any help with this problem. 

[parry2k · Accepted solution]

add a measure 

Counts = 
var cnt = Count(Table1[Product Sold])
return cnt * if(MAX(Table2[Role ID]) = 2,3, 1) 

change the table name/column name as it fit in your data model.

[anonymous188]

This solution worked for display purposes, but the SUM is incorrect somehow. Rather than applying the logic based on the Role ID, the resulting value is the sum of every count multiplied by 3.

In other words, this is the resulting sum:

Employee ID	Counts
111	1
112	2
113	9
Total	18

Any idea what a fix might be?

[parry2k]

change max to sum and see