Counting number of rows of unique column A and B combination then divide by total number of unique A

[Anonymous]

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

[mwegener · Accepted solution]

Hi @Anonymous 

check this out.

PBIX

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.