Forum Discussion
different calculation depending on ID
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.
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.
3 Replies
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.
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?
change max to sum and see
