Forum Discussion
Helper IIIShow 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
Can anyone please show me how I can go about this? Thank you Thank you Thank you!
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
4 Replies
v-danhe-msftMicrosoft Employee
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
- 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