Hi I believe I have a very difficult problem....
I have a table like this:
| Month | Distributor | Seller |
| Oct-19 | A | Ben |
| Oct-19 | B | Tom |
| Oct-19 | C | Mary |
| Oct-19 | D | Ben |
| Dec-19 | A | Mary |
| Dec-19 | B | Ben |
| Dec-19 | C | John |
| Jan-20 | A | Ben |
| Jan-20 | B | Tom |
I need to create a new column.
This column will count the number of unique Distributor and Seller combinations (ie A-Ben is different from B-Ben), THEN divide by the number of rows which contains the Seller.
For example, expected output is below:
| Month | Distributor | Seller | New column |
| Oct-19 | A | Ben | 0.50 |
| Oct-19 | B | Tom | 1 |
| Oct-19 | C | Mary | 0.5 |
| Oct-19 | D | Ben | 0.25 |
| Dec-19 | A | Mary | 0.5 |
| Dec-19 | B | Ben | 0.5 |
| Dec-19 | C | John | 1 |
| Jan-20 | A | Ben | 0.5 |
| Jan-20 | B | Tom | 1 |
Explanation. First row.
In the whole table, there are 2 A-Ben combinations. In the whole Seller column, there are 4 rows which contain Bens. So the ratio in the new column would be 2/4 = 0.5.
Second row.
In whole table, there are 2 B-Tom combinations. In the Seller column, there are 2 rows which contains Tom. So ratio = 2/2 = 1.
Lastly... the ratio needs to be filtered by time!!!!
If this is too hard, please teach me how to do it WITHOUT time filter....
Thanks in advance
