Show duplicate/common data between 2 items in the same column + Calculate Average Cost

[trdoan]

Hi everyone,

I have the following table called "Data":

Vendor	Group	Model	Quantity	Cost	TAT	Posting Date
A	S	A150	150	450	67	July 7, 2018
A	M	A200	250	1500	75	June 22, 2018
A	M	A150	25	8500	85	July 9, 2018
A	L	A200	350	1250	125	March 5, 2018
A	XL	A500	150	6500	45	February 20, 2018
A	M	A900	385	475	40	January 29, 2018
A	M	A150	650	45	45	August 31, 2018
B	M	A150	65	7500	15	April 10, 2018
B	M	A300	140	3420	10	April 3, 2018
B	S	A150	20	10525	85	January 3, 2018
B	S	A150	30	10500	40	June 3, 2018
B	S	A150	450	450	64	April 3, 2018
A	XS	A900	45	75	60	January 3, 2018
A	M	A900	95	655	175	January 3, 2018
A	XL	A300	15	21500	25	January 3, 2018
A	S	A500	450	65	25	May 3, 2018
A	M	A350	250	450	22	January 3, 2018
B	S	A150	45	8500	28	January 3, 2018
A	S	A300	550	650	128	January 3, 2018
A	M	A150	1500	855	190	January 3, 2018
B	M	A150	65	1750	41	January 3, 2018
B	L	A500	75	1700	24	January 3, 2018
B	S	A900	55	9800	37	May 29, 2018
B	M	A500	150	850	83	April 18, 2018

I'd like to compare A & B by only their common groups and/or models and calculate their corresponding Average Costs, Average TAT & Total Volume.

I'm thinking of 2 graphs where:

1.  The first graph shows all common groups between A & B, ignoring uncommon items

2. The second graph shows all common models between A & B, ignoring uncommon items

Can anyone please show me how I can go about this? Thank you Thank you Thank you!

[v-danhe-msft]

Hi @trdoan ,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @trdoan,

Could you please offer me more information about  common groups and/or models?

Could you want to calculate avg data based on the group and model?

If so, you could refer to below calculated table:

Common model = SUMMARIZE('Table1','Table1'[Model],"Avg quantity",AVERAGE(Table1[Quantity]),"Avg cost",AVERAGE(Table1[Cost]),"Avg TAT",AVERAGE(Table1[TAT]))

Result:

Common group = SUMMARIZE('Table1','Table1'[Group],"Avg quantity",AVERAGE(Table1[Quantity]),"Avg cost",AVERAGE(Table1[Cost]),"Avg TAT",AVERAGE(Table1[TAT]))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[trdoan]

Hi @v-danhe-msft, thank you for your help so far. Apologise for any confusion but what I'd really want to see is the groups that both A & B have in common.

For example, in the provided sample, the common group that A & B share are: S, M, & L. Then, after finding out what those groups are, I'd like to calculate their corresponding Average Costs.

I plan to use a graph for this task, however, I'm not sure if it should be calculated column(s) or measure(s) or both. Please advise!

Thank you very much!

[v-danhe-msft]

Hi @trdoan ,

Based on my test, you could refer to below steps:

Create a calculated table:

Test = SUMMARIZE('Table1','Table1'[Group],"a",CONCATENATEX('Table1','Table1'[Vendor],","))

Create a caclculate column to group the "common group":

Common Group = AND(SEARCH("A",'Test'[a],,BLANK()),SEARCH("B",'Test'[a],,BLANK()))

Then you could calculate the average value for different item:

Average quantity = CALCULATE(AVERAGE(Table1[Quantity]),FILTER('Table1','Table1'[Group]='Test'[Group]))

You could also download the pbix file to have a view.

Regards,

Daniel He