Forum Discussion
Group Names to different numbers
Hi people, maybe you can help me with my approach:
The starting position is this:
There are four Teams, Team AAA, Team BBB, Team CCC and Team DDD
I also have a column with OrderNumbers, which can be used by multiple teams which means they can also appear multiple times in a column.
There are also UnderOrderNumbers, which are related to the OrderNumbers.
Every OrderNumber can have multiple UnderOrderNumbers. Every UnderOrderNumber is unique.
My goal is to show how many OrderNumbers are used by multiple teams,
E.g. team AAA and BBB worked together on 10 OrderNumbers
And I also want to show how many OrderNumbers were used exclusively by one Team
E.g. Team AAA used 20 order numbers.
I’m glad for any help!
Hi, Anonymous
Please check the sample file. pbix attached
If it helps, please refer to these steps
1.You need to modify you table as below,please see steps in advanced editor.
2. applied text filter in columns for "Duplicate table" and "Duplicate_duplicate table" ,then " close&apply" to exit "Transform Data"
3. Use matrix to present your data.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Is that helpfull in any sense? I can't put on the real dataset. But I think this one shows the possibilitys.
First 4 rows: Team AAA and BBB worked together on one OrderNumber. (1111)
Row 5-8: Team AAA,BBB, CCC and DDD worked together on one OrderNumber (2222)
Row 9: Team CCC worked alone on one order Number (3333)
At the end I want to have a diagram which shows that
Team AAA and BBB worked on one Order together and Team AAA,BBB,CCC and DDD worked on one together
The second diagram would show, that Team CCC worked alone on one Order.
Edit.: It could be possible to do it just with the OrderNumber. But I'm not sure how. I did it in Excel with the UnderOrderNumber and the OrderNumber and two Pivots
- v-easonf-msft
Community Support
Hi , Anonymous
I am not sure what desired result would you want.Could you please share your desired output in excel or screenshots for further analysis?
You can take a try the function of "Pivot column " in powerbi advance editor (Transform Data) and "close&apply" it.
Best Regards,
Community Support Team _ Eason
- v-easonf-msft
Hi , Anonymous
Could you please tell me whether your problem has been solved?
If you haven't, please feel free to ask.Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi v-easonf-msft thanks for the support and the request.
The problem hasn't been solved yet, but I did an excel to show the relationsships and the output that I want to create:
The two pivot tables on the right, are what I want to show.
The first table on the left is originally also a pivot table. For the purpose of showing what I want to create, I just put some in some random numbers and team names.
The original Pivot showed the OrderNumbers in the Rows, the Teams in the Columns and the amount of UnderOrderNumbers in the Values.
The relation for the second table (starts at G4) is shown in the formula bar .
The relation for the third table (starts at L4) would be:
L4 =TRIM(G4&" "&H4&" "&I4&" "&J4)
The two pivot table on the right are just showing the Teams in the Rows and the Values of Amount (shown in the picture below)
I hope that shows what my goal is 🙂 Thanks in advance for any ideas and help!