How to create a summary table of what get from a matrix with hierarchy

[jvirgi]

I have a dataset that looks like this where i have a hierarchy that i want to summarize by.  Each row would be a count of widgets for a given area.  All levels are not always known.  Here is some raw data.

 I want to get a table that has all layers of the hierarchy in a single column with the sum'd totals in a 2nd column.  This matrix with a hierarchy essentially gives me this. 

But how would i get a table more like this with the Path included?  I may also have additional rows for years so that would be another column as well.   Can this be created as a whole new table from the original with transformations?

see below for sample pbix

https://drive.google.com/file/d/1ugFZRXvJW24WFD99xz5_R2c9huXDQ5nH/view?usp=sharing 

[jvirgi]

@Ashish_Mathur Thanks.  I was having a look.   How would you handle the fact that each version and subbrand could have duplicate names under different brands. (eg. there could be a 5 pack under any of the brands.)

[Ashish_Mathur]

You are welcome.  Sorry but i cannot understand your question.

[jvirgi]

@Ashish_Mathur  If you dont make the charts with all levels of the hierarchy, then it would improperly aggregate the data.  For example if you removed Subbrand from this matrix, it would aggregate both the 2packs as if they were the same product.  

I wonder if to keep this relationship we need to merge the hierarchies to use for the relationships between tables.  For example make the Version table join to comment by a new column in both which would be =Brand & Subbrand & Version.  

[Ashish_Mathur]

Hi,

In the Query Editor, right click on the Path and Widgets columns and select "Unpivot other columns".

[jvirgi]

@Ashish_Mathur  Thanks for the reply.  It doesn't seem to give the desired table.  Heres what that gives:

[Ashish_Mathur]

Hi,

I cannot get to your desired result because i cannot appreciate your desired data end result.  Why are you not contended with dragging the 3 level fields to the rows section of your matrix visual?  What information does that not give you?

[jvirgi]

@Ashish_Mathur 

Here is what i'm looking to do,  maybe this explains it better.  I have 2 tables i need to combine:

• Table of Comments related to products
• Table of Sales for these products.

I want to be able to combine these tables of data so i can calculate a #comments / Sales for each level of the hierarchy  --- do this at a brand, subbrand and version level.   Sometimes comments dont have all the information as in the example.   

2 tables that i'd have:

Desired output after combining the data:

Here is a new PBIX with this information

https://drive.google.com/file/d/19DB-kGhCX27q8sPuLaKeDJwpUKlvyMfl/view?usp=sharing 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.