Comparing multiple Organizations with Base Organization

[Guneet_B21]

Hi Everyone,

i have a dataset with org codes and routes (please find a sample below),

i want to fix 'A' as my base client and i want to compare the base routes to the routes of all other clients;

then further show the differences in a table against each organizations 

Sample:

Route	Org Code
Bangladesh-Chattogram-Durban-Durban-Port Elizabeth-Standard	B
Belgium-Antwerpen-Direct-Durban-Durban-Standard	A
Belgium-Antwerpen-Direct-Durban-Durban-Standard	B
Belgium-Antwerpen-Direct-Port Elizabeth-Port Elizabeth-Standard	A
Belgium-Antwerpen-Direct-Port Elizabeth-Port Elizabeth-Standard	C
Belgium-Antwerpen-Direct-Port Elizabeth-Port Elizabeth-Standard	B
Belgium-Antwerpen-Durban-Durban-Johannesburg-Standard	A
Belgium-Antwerpen-Durban-Durban-Johannesburg-Standard	C
Belgium-Antwerpen-Durban-Durban-Johannesburg-Standard	B
Belgium-Antwerpen-Port Elizabeth-Port Elizabeth-Cape Town-Express	A
Belgium-Antwerpen-Port Elizabeth-Port Elizabeth-Cape Town-Express	C
Belgium-Antwerpen-Port Elizabeth-Port Elizabeth-Cape Town-Express	B
Belgium-Antwerpen-Port Elizabeth-Port Elizabeth-Cape Town-Standard	A
Belgium-Antwerpen-Port Elizabeth-Port Elizabeth-Cape Town-Standard	C
Belgium-Antwerpen-Port Elizabeth-Port Elizabeth-Cape Town-Standard	B
Belgium-Antwerpen-Port Elizabeth-Port Elizabeth-Durban-Express	A
Belgium-Antwerpen-Port Elizabeth-Port Elizabeth-Durban-Express	B
Belgium-Antwerpen-Port Elizabeth-Port Elizabeth-Johannesburg-Express	A
Belgium-Antwerpen-Port Elizabeth-Port Elizabeth-Johannesburg-Express	C
Belgium-Antwerpen-Port Elizabeth-Port Elizabeth-Johannesburg-Express	B
Belgium-Antwerpen-Richards Bay-Richards Bay-Durban-Express	C
Brazil-Itajai-Durban-Durban-Cape Town-Standard	B

Eg:-

when i compare A with B
i should see the following

Bangladesh-Chattogram-Durban-Durban-Port Elizabeth-Standard	B
Brazil-Itajai-Durban-Durban-Cape Town-Standard	B

[ryan_mayu]

@Guneet_B21 

you can try this to get the result

Table 2 =

VAR _compare1="A"

VAR _compare2="B"

return ADDCOLUMNS(EXCEPT(SELECTCOLUMNS(FILTER('Table','Table'[Org Code]=_compare2),"Route",'Table'[Route]),SELECTCOLUMNS(FILTER('Table','Table'[Org Code]=_compare1),"Route",'Table'[Route])),"org code",_compare2)

pls see the attachment below

[Guneet_B21]

Thanks @ryan_mayu ,
but can you tell me how do i compare all the other routes simultaneuosly, not just 1

[ryan_mayu · Accepted solution]

@Guneet_B21 

pls see if this is what you want. you can select differnt org code to see different result

[Guneet_B21]

Thanks @ryan_mayu ,

works perfectly

[ryan_mayu]

you are welcome

[Guneet_B21]

Hi @ryan_mayu ,

I was wondering if there would be a way by which i dont have to select the CompB,
need to show the comparison of all the other organization against base client
and need to show that list in a table

[ryan_mayu]

what's the expected output based on the sample data we have?

[Guneet_B21]

If we take A as base client,

this should be our desired result:

Bangladesh-Chattogram-Durban-Durban-Port Elizabeth-Standard	B	Extra
Brazil-Itajai-Durban-Durban-Cape Town-Standard	B	Extra
Belgium-Antwerpen-Direct-Durban-Durban-Standard	C	Missing
Belgium-Antwerpen-Port Elizabeth-Port Elizabeth-Durban-Express	C	Missing
Belgium-Antwerpen-Richards Bay-Richards Bay-Durban-Express	C	Extra

Missing means the data in A not in but in other org,
Extra means data not in A but still in other org

[ryan_mayu]

I think we can do that for extra, but I don't know how to do that for missing. 

It's becuase it will automatically create new data.

e.g we have org code, a-z, we only have one route for a, then the output will be 25 rows for b-z and mark as missing.

Let's see if anyone else have better solution for this