Removing Blank rows in the middle hierarchy in Matrix

[likhitha2705]

Hello Team,
I've a product Hierarchy like this.
Category->Sub Category->Product Name.
Few Categorirs have Sub Categories but not Products,
Few Categories have Products but not Sub Categories. And the data looks like this.

For Accessories there's no Sub category but Products are there.In my output I want to see Accessories followed by the Acceessories Products but not the Blank.
I'm using the below DAX

Sales_Test =

SWITCH(TRUE(),

ISINSCOPE(Sheet1[Product Name])&&SELECTEDVALUE(Sheet1[Product Name])=BLANK(),BLANK(),

ISINSCOPE(Sheet1[Sub Category])&&SELECTEDVALUE(Sheet1[Sub Category])=BLANK(),BLANK(),

ISINSCOPE(Sheet1[Category])&&SELECTEDVALUE(Sheet1[Category])=BLANK(),BLANK(),SUM(Sheet1[Sales]))
But the output show like this

In my output I want to see Accessories followed by the Acceessories Products.And for the rest of the Category the output is correct.
Let me know how to achieve this.
TIA

[likhitha2705]

Hi @MNedix 
Thanks for the reply .Somehow I'm little confused with your solution. If possible can attach the pbix file for the above.
TIA

Category	Sub Category	Product Name	Sales
Accessories		Bike Wash	1000
Accessories		Fender Set	2000
Accessories		Helmet	3000
Bikes	Mountain Bikes		2500
Bikes	Road Bikes		3000
Bikes	Touring Bikes		4500
Clothing	Caps	Logo Cap	5000
Clothing	Gloves	Finger Gloves	4300
Clothing	Jerseys	Long Sleeve Jersey	3500

[MNedix · Accepted solution]

Heya @likhitha2705 ,

See attached file. I've simply created two calculated columns as previously mentioned. However, if your data is too big, if you have thousands of entries and tenths of these blank situations then you may want to consider a different approach. Never the less, the cleaner the hierarchy, the cleaner the output.

[likhitha2705]

Hi @MNedix 
Thanks for the reply .Somehow I'm little confused with your solution. If possible can attach the pbix file for the above.
TIA

Category	Sub Category	Product Name	Sales
Accessories		Bike Wash	1000
Accessories		Fender Set	2000
Accessories		Helmet	3000
Bikes	Mountain Bikes		2500
Bikes	Road Bikes		3000
Bikes	Touring Bikes		4500
Clothing	Caps	Logo Cap	5000
Clothing	Gloves	Finger Gloves	4300
Clothing	Jerseys	Long Sleeve Jersey	3500

[MNedix]

Heya,

To be sure you get accurate and correct results from your data you should clean it up first and make it consistent across the tables. The first thing you should do is complete/clean-up your hierarchy: create calculated Sub-Categories for orphan Products and add Products where you have none. 
For example, you could use a Switch function to create a Subcategory from Products - for example:

Sub-Category = 
SWITCH(
TRUE(),
[Product] = "Bike Wash", "Bike accessories",
[Product] = "Fender Set", "Bike accessories",
[Product] = "Helmet", "Bike accessories")

 You can also create a Product from Sub-Category, like this:

Product=
IF(ISBLANK([Product]),[Sub-Category])

The above will allow you to:

1. Clean up the orphan Products.

2. Have prices correctly associated with Products rather than Categories or Sub-Categories

Then you could start building a proper hierarchy structure, calculate the PATH and use ISINSCOPE to correctly display the table/values.