cancel
Showing results for
Did you mean:
Highlighted
Member

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

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

Hi @trdoan,

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:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super Contributor

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

Hi @trdoan,

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:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

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

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!

Super Contributor

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

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]))`

Regards,
Daniel He
Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super Contributor

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

Hi @trdoan ,

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 51 members 1,211 guests
Recent signins: