Compare diferent rows and show the common values

[eravelo]

Hi, I have a dataset that is an append of 3 different datasets (each corresponding to one zone, America, Europe and Asia)

The dataset is something like this:

Month     Zone      Vendorcode       Vendor        Values1   Values2      Values3
Jan           Asia            0001             Vendor1           1              0               0
Jan           Asia            0001             Vendor1           1              1               1
Jan        America         0001             Vendor1           0              1               0
Jan         Europa          0002             Vendor2           1              1               1
Jan         Europa          0002             Vendor2           1             1                1
Jan        America        0001              Vendor1           1             0                 0
Feb           Asia           0003              Vendor3           0             1                 0 
Feb          Asia            0001               Vendor1          0             1                 0
Feb         America        0001              Vendor1          0            1                 0
Feb           Europa        0002              Vendor2          0            0                  0
Feb           Europa        0001              Vendor1         1            1                  1
Feb          America       0002               Vendor2         1            1                  1
March        Asia           0003               Vendor3        1              0                   0
March         Asia           0001              Vendor1         1             1                  1
March       America        0002             Vendor2        1              1                  1
March        Europa         0001             Vendor1         1             1                  1
March        Europa           0003             Vendor3       1             1                  1
March        America          0002             Vendor2      1             1                  1

I need to compare the diferent rows by vendor and zone so if for example the vendor 1 appears in asia, europe and america, i need it to appear in a table with the sumarized values from the table and depending on the selected filters. but if vendor 2 is not in all the zones it will not appear in the table. 

by the example table the expecting result is something like this: 

Vendorcode	Vendor	Values1	Values2	Values3
0001	Vendor1	0.66666667	0.77777778	0.44444444
0003	Vendor3	0.75	0.75	0.5

Do you have any idea of how to do that?

Thanks

[eravelo]

Hello, I have a dataset that is an append of 3 different datasets (one for each zone, America, Asia and Europe), the table looks something like this:

Spoiler

Month	Zone	Vendorcode	Vendor	Values1	Values2	Values3
Jan	Asia	0001	Vendor1	1	0	0
Jan	Asia	0001	Vendor1	1	1	1
Jan	America	0001	Vendor1	0	1	0
Jan	Europa	0002	Vendor2	1	1	1
Jan	Europa	0002	Vendor2	1	1	1
Jan	America	0002	Vendor1	1	0	0
Feb	Asia	0003	Vendor3	0	1	0
Feb	Asia	0001	Vendor1	0	1	0
Feb	America	0001	Vendor1	0	1	0
Feb	Europa	0002	Vendor2	0	0	0
Feb	Europa	0001	Vendor1	1	1	1
Feb	America	0002	Vendor2	1	1	1
March	Asia	0003	Vendor3	1	0	0
March	Asia	0001	Vendor1	1	1	1
March	America	0002	Vendor2	1	1	1
March	Europa	0001	Vendor1	1	1	1
March	Europa	0003	Vendor3	1	1	1
March	America	0003	Vendor3	1	1	1

what i need to do is compare the diferent rows so if for example vendor 1 apears in europe, asia and america, then it will appear in a table on the dashboard with its different values summarized (in average for example) and filter by the possible filters in the dashboard. But if vendor 2 appears in 2 zones instead of all of them then vendor 2 is not going to appear in the table.

the expected table should be something like this: 

Spoiler

Vendorcode	Vendor	Values1	Values2	Values3
0001	Vendor1	0.66666667	0.77777778	0.44444444
0003	Vendor3	0.75	0.75	0.5

Do you have any Idea of how to do this?  if you think that some reference colums could be added and how to use them... please let me know... 

Thank you a lot!

[Eric_Zhang · Accepted solution]

@eravelo

At first, create a calculated table and link those two tables with proper relationship.

Zone Table = DISTINCT(yourTable[Zone])

Then create those measures

isContainsAllSelectedZones = 
VAR selectedZones = CONCATENATEX(ALLSELECTED('Zone Table'[Zone]),'Zone Table'[Zone],",",'Zone Table'[Zone])
VAR vendorZones = CONCATENATEX(VALUES(yourTable[Zone]),yourTable[Zone],",",yourTable[Zone]) 
RETURN selectedZones=vendorZones

average of val1 = IF([isContainsAllSelectedZones],AVERAGE(yourTable[Values1]),BLANK())

average of val2 = IF([isContainsAllSelectedZones],AVERAGE(yourTable[Values2]),BLANK())

average of val3 = IF([isContainsAllSelectedZones],AVERAGE(yourTable[Values3]),BLANK())

See more details in the attched pbix file.

[Ashish_Mathur]

Hi,

You may refer to my solution here.