cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
trdoan Member
Member

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

Hi everyone,

 

I have the following table called "Data":

 

VendorGroupModelQuantityCostTATPosting Date
ASA15015045067July 7, 2018
AMA200250150075June 22, 2018
AMA15025850085July 9, 2018
ALA2003501250125March 5, 2018
AXLA500150650045February 20, 2018
AMA90038547540January 29, 2018
AMA1506504545August 31, 2018
BMA15065750015April 10, 2018
BMA300140342010April 3, 2018
BSA150201052585January 3, 2018
BSA150301050040June 3, 2018
BSA15045045064April 3, 2018
AXSA900457560January 3, 2018
AMA90095655175January 3, 2018
AXLA300152150025January 3, 2018
ASA5004506525May 3, 2018
AMA35025045022January 3, 2018
BSA15045850028January 3, 2018
ASA300550650128January 3, 2018
AMA1501500855190January 3, 2018
BMA15065175041January 3, 2018
BLA50075170024January 3, 2018
BSA90055980037May 29, 2018
BMA50015085083April 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!

1 ACCEPTED SOLUTION

Accepted Solutions
v-danhe-msft Super Contributor
Super Contributor

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

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:

1.PNG

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

Result:

1.PNG

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

 

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.

View solution in original post

4 REPLIES 4
v-danhe-msft Super Contributor
Super Contributor

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

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:

1.PNG

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

Result:

1.PNG

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

 

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.

View solution in original post

trdoan Member
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!

v-danhe-msft Super Contributor
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()))
1.PNG
Then you could calculate the average value for different item:
Average quantity = CALCULATE(AVERAGE(Table1[Quantity]),FILTER('Table1','Table1'[Group]='Test'[Group]))
1.PNG
You could also download the pbix file to have a view.
 
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.
v-danhe-msft Super Contributor
Super Contributor

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

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

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 51 members 1,211 guests
Please welcome our newest community members: