Visualization of overlap between users belongings

[ablalm]

Hi there,

I have a list of stockholders, their unique id, name, stockname and how much do they have. In one row only information about one user and one stock type. I need to find out  with whom have the most overlap stocks one exact user. 

In the screenshots the example of the sheet i have. And the result that I want to get for one user (George).

Thank you. 

[tamerj1]

Hi @ablalm 
Please refer to attached sample file with the proposed solution.

[ablalm]

Thank you!

[FreemanZ]

hi @ablalm 

your data seems inconsistent, like 3 is Tom or Ivan?

[ablalm]

hi @FreemanZ 

sorry for that, here is the correct output

[FreemanZ · Accepted solution]

hi @ablalm 

try to 

1) add a calculated table like:

SlicerTable = ALL(TableName[user_name])

2) add a slicer with SlicerTable[user_name]

3) plot a table visual with TableName[user_name], TableName[user_id] and a measure like:

OVERLAP = 
VAR _name1 =MAX(TableName[user_name])
VAR _name2 =SELECTEDVALUE(SlicerTable[user_name])
VAR _list1 =
CALCULATETABLE(
    VALUES(TableName[stocks]),
    ALL(TableName[user_id]),
    TableName[user_name] = _name1
)
VAR _list2 =
CALCULATETABLE(
    VALUES(TableName[stocks]),
    ALL(TableName[user_id]),
    TableName[user_name] = _name2
)
VAR _count =
COUNTROWS(INTERSECT(_list1, _list2))
RETURN
IF(
    _name1=_name2,
    BLANK(),
    _count
)

it worked like:

[ablalm]

Thanks a lot!