Group Names to different numbers

[Anonymous]

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!

[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.

[Anonymous]

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!

[EricHulshof]

Could you maybe create a sample dataset? With all the relationships etc. It would be easier to help you that way. 
Remember to not post any sensitive data while doing this. 

[Anonymous]

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]

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

[EricHulshof]

Something like this? 

On the left you see wich teams worked on what order number and howmany subordernumbers there are. On the right its just a 1 if they worked on that ordernumber, no matter howmany subordernumbers there are.

I used the matrix visual for this, put team under rows, ordernumber on columns. On the left i added Count of ordernumber on values, on the right i used a measure on values:

Measure = IF(MAX('Table'[OrderNumber]),1,0)

[Anonymous]

Thanks @EricHulshof 

I think the measure you wrote down, is a good start!

Then I think I would need a second measure, which defines new variables fot the possbile team combinations e.g. (AAAA, AAAA & BBBB, AAAA & BBBB & CCCC... etc.)

Would it make sense to combine it into one measure or could I just mention the first on in the second measure?

Or would it be easier to create a new column or table with all possible Team combinations and link it in some way to the orders?

[v-easonf-msft · Accepted solution]

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.

[Anonymous]

Thank you @v-easonf-msft that worked out beautifully! Really appreciate the help!

Just for the records: Do you think it would be in general possible, do do this with measures? Without creating new tables?

In any case: I'm really happy with this solution!