Visualize Data in Matrix Table based on Dropdown - Issue with Dropdown

[tim_001]

Dear Community, 

I am having some issues with visualization of a matrix table. I hope it can be solved either with changing the layout of the visual or with an additional measure. There is no possibility to change the data set much. 

DATA:

Product Group	Product	Match Level	Stock Keeping Unit (SKU)
Fruits	Apple Red	Level 1	10012
Fruits	Apple Green	Level 1	10145
Fruits	Apple Yellow	Level 1	10784
Vegetables	Tomato	Level 1	10185
Vegetables	Tomato	Level 1	10156
Fruits	Banana	Level 1	10151
Fruits	Apple Red + Green	Level 2	10012
Fruits	Apple Red + Green	Level 2	10145
Fruits	Apple Red + Green	Bundle	10121

- Stock information is taken from another table linked to the SKU. 

AS-IS: 

- DropDown with single select by Product. 

- Matrix Visual to show stock by Product:

Current Visual Result when selecting Product "Apple Red + Green":

Product	Stock
Apple Red + Green	3643

Issue: I cannot see how it is split down. 

TO-BE: 

- DropDown with single select by Product. 

- Matrix Visual to show stock by Product:
Example "Apple Red + Green" how it should look like:

Match Level	Product Group	Stock
Bundle	Apple Red + Green	23
Level 2	Apple Red + Green	234
Level 1	Apple Red	56
Level 1	Apple Green	63

--> The idea is to see how many we have in the different levels. They should not all be summed up but just the total of all the SKUs linked to the same Product. 

Example "Apple Red" how it should look like:

Match Level	Product Group	Stock
Bundle	Apple Red + Green	23
Level 2	Apple Red + Green	234
Level 1	Apple Red	56
Level 1	Apple Green	63

--> The idea is to see how many we have in the different levels. They should not all be summed up but just the total of all the SKUs linked to the same Product. 

The Challenge: 

- I am not sure how to visualize it as the single select dropdown lets me select only one product name. But to make it multiple select is not working as I don´t know which product has how many levels. 

Maybe there is something I am overseeing or a trick that some of you might know how to get it working. 

Looking forward to your replies and as always thanks for any inputs 🙂

Kind Regards Tim

[lbendlin]

There is no possibility to change the data set much. 

Your data is not in a format that is usable in Power BI.

[tim_001]

Thanks a lot for the feedback. Do you know how I could restructure the table to get the desired result?

[lbendlin]

You have many options to represent your ragged hierarchy - Here is one example (and it links to others)

Parent-Child Hierarchies with multiple parents in Power BI with Power Query (thebiccountant.com)

The table structure (and the data model) ultimately have to follow the user requirements.  So if you need to be able to filter by product then you need to have a product column at the atomic level (and the associated hierarchy).

[tim_001]

Thanks a lot. It looks like I need to spend some time to figure it out in detail but I get the idea. 

Is it correctly understood that first step would be changing the table to look similar like that?: 

Because I have the data in a data-base friendly format, could I not create another table in Power BI itself and make a function if it is Level 1 then put it in the columne, otherwise empty? 

I am just asking because it was mentioned that I would need to change my dataset but if I could do it like that, then I would not have to change the data set but only transform it into another table. Not sure if it will work 🙂 It sounds a bit too optimistic from my end. Thanks a lot again for the tips and links. 

[lbendlin]

Something similar to your example (although not your geographical choices 🙂 )  may work.  You are dealing with a scenario that has both multiple parents AND a ragged hierarchy. So you need to accommodate both when you transform the data.

[tim_001]

Thanks so much. Yes, I think I am getting there. It looks like this now: 

Product	Stock Keeping Unit	Level 1	Level 2	Bundle
Apple Red	10012	Apple Red	Apple Red + Green
Apple Green	10145	Apple Green	Apple Red + Green
Apple Yellow	10784	Apple Yellow
Tomato	10185	Tomato
Banana	10151	Banana
Apple Red + Green	10121			Apple Red + Green

The challenge is now on the Apple Red + Green with a new SKU. So I am not sure how to apply it to the new table format. But maybe I also did not understand the nodekey exactly. 

Any ideas are appreciated 🙂