Forum Discussion
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:
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
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
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!
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