Hierarchy levels (nodes) display issue in power bi desktop

[Atl]

Hello everyone,

 My power bi desktop is getting data from the attached excel file. I am using matrix visual. Please look at my report output. Revenue is showing in three different levels because Subcategory, ParentCategory and GrandParentCategory all have the same value. How do i display only one revenue node if all Subcategory, ParentCategory and GrandParentCategory all have the same value. I do still need to display all four nodes operating expenses, practice operating expenses, personnel costs and employee taxes & benefits since they all have different values. I do have access to a master excel file so if changing excel is easier then i can do it in excel or i can do it in power bi if easier there. I already tried leaving values blank but empty rows(nodes) still showing up.  Please help.  

DWGLACCOUNTID	SSGLACCOUNTID	SSGLACCOUNTIDOTHER	GLCATEGORY	RECORDCOUNT	Subcategory	ParentCategory	GrandParentCategory
23|406	406	50600	Employee Taxes & Benefits	1	Personnel Costs	Practice Operating Expenses	Operating Expenses
23|416	416	60050	Occupancy Expense	1	Practice Operating Expenses	Practice Operating Expenses	Operating Expenses
23|420	420	60250	Occupancy Expense	1	Practice Operating Expenses	Practice Operating Expenses	Operating Expenses
23|58	58	50050	Employee Salaries & Wages	1	Personnel Costs	Practice Operating Expenses	Operating Expenses
23|858	858	50230	Employee Salaries & Wages	1	Personnel Costs	Practice Operating Expenses	Operating Expenses
23|877	877	65210	Marketing	1	Other Opex	Practice Operating Expenses	Operating Expenses
23|451	451	63200	All Other Expenses	1	Other Opex	Practice Operating Expenses	Operating Expenses
23|503	503	66050	All Other Expenses	1	Other Opex	Practice Operating Expenses	Operating Expenses
23|372	372	40160	Patient Net Revenue	1	Revenue	Revenue	Revenue
23|368	368	40120	Patient Net Revenue	1	Revenue	Revenue	Revenue
23|590	590	78400	Depreciation & Amortization	1	Operating Expenses	Operating Expenses	Operating Expenses
23|597	597	82100	Other Revenue	1	Revenue	Revenue	Revenue
23|596	596	82050	Other Revenue	1	Revenue	Revenue	Revenue

[Anonymous]

Hi,@DataNinja777,I am glad to help you. 
Has your problem been solved?
If you have found suitable solutions, please share them as it will help more users with similar problems.
For example, relevant code or links to articles.
Or you can mark the valid suggestions provided by other users as solutions.
Thank you very much for your understanding and support of Power BI.

[Anonymous]

Hi,DataNinja777 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@Atl .I am glad to help you.
The matrix hierarchy in Power BI is actually hierarchical based on the fields and the values within them, and the matrices provided by default in Power BI will duplicate the display of fields at different levels, even if the data between them is identical. This is because the matrix needs to show the final aggregated data (i.e. the data referred to by the smallest level)
If you have multiple levels with the same data, but only want to show it once (not repeatedly).
This is difficult to accomplish in power bi's matrix visual. This is because it does not correspond to the essence of the matrix hierarchical presentation.
Matrices need to be indented hierarchies to inform the user where the data is located, and all the data is divided according to the hierarchy.
I think a better way to do this would be to use symbols, such as arrows, for neighboring hierarchies that have the same value to refer to the value of the previous hierarchy. (This doesn't destroy the overall structure of the matrix and ensures that the final data is displayed well.)
Like this:

I appropriately modified the code provided by DataNinja777 to create new calculated columns
Essentially, it's similar to the advice DataNinja777 offers.

ParentCateogry_Level_Flag = 
IF(
    'Table'[Subcategory] = "Revenue" && 
    'Table'[ParentCategory] = "Revenue" && 
    'Table'[GrandParentCategory] = "Revenue", 
    "↑", 
    'Table'[ParentCategory]
)



SubCateogry_Level_Flag = 
IF(
    'Table'[Subcategory] = "Revenue" && 
    'Table'[ParentCategory] = "Revenue" && 
    'Table'[GrandParentCategory] = "Revenue", 
    "↑", 
    'Table'[Subcategory]
)

This will minimize the occurrence of the same data to some extent, and you need to be careful about the order in which you place the fields.

Data hiding of the matrix by measure will probably hide the final aggregated values to be displayed, destroying the final effect of the matrix.(When you try to hide a level in the matrix, all data under that level is hidden.)
While using customized symbols instead of duplicated values in neighboring levels will maximally protect the entire structure of the matrix. (The data in the matrix must have the same number of levels, and for field levels with empty data, the matrix displays blank instead of not displaying the level)

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Atl]

Hi @Anonymous , thank you for detailed reply. I appreciate your alternative approach. My stakeholder do need like to see the empty rows or repetitive values so i explained them that is not possible with matrix visual in the power bi. Thank you for trying. Best, 

[Anonymous · Accepted solution]

Hi,@Atl .Thanks for your reply.
There are some limitations in the Power BI matrix visual, which are also related to its own design features. Thank you for your understanding and support of Power BI.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Atl]

Hi, thank you. i completely understand each platform will have some limitations. Power bi is still way ahead compared to netsuite and sigma. In the near future it would be nice if power bi include parent- child options for hierarchies similar to netsuite reporting. best,

[Anonymous]

Hi,@Atl .
Thank you very much for understanding the features in Power BI that are not supported by Matrix at this time.
You can mark this reply of yours as a solution so that more people can quickly find a suitable alternative when the same doubt exists.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataNinja777]

Hi @Atl 

In Power BI, when using a matrix visual, the issue of displaying duplicate revenue nodes arises because the Subcategory, ParentCategory, and GrandParentCategory fields all contain "Revenue." To display only one revenue node while keeping other categories intact, one approach is to modify the data within Power BI using a calculated column. A new column can be created in Power Query or via DAX that identifies redundant revenue entries and replaces them with a single label.

Revenue_Level_Flag = 
IF(
    'Table'[Subcategory] = "Revenue" && 
    'Table'[ParentCategory] = "Revenue" && 
    'Table'[GrandParentCategory] = "Revenue", 
    "Revenue (Keep Only One)", 
    'Table'[Subcategory]
)

This column should be used in the matrix visual instead of the original Subcategory field, ensuring that Revenue appears only once while other categories remain unchanged. If modifying the source data in Excel is an option, removing duplicate rows manually or adding a new column to flag duplicates can also help. In Excel, a formula can be used to achieve the same logic before importing data into Power BI.

=IF(AND(A2="Revenue", B2="Revenue", C2="Revenue"), "Revenue (Keep One)", A2)

Alternatively, if modifying the dataset is not feasible, a measure can be created to dynamically filter out duplicate Revenue nodes by checking if all three levels contain "Revenue."

Revenue_Filter = 
IF(
    SELECTEDVALUE('Table'[Subcategory]) = "Revenue" &&
    SELECTEDVALUE('Table'[ParentCategory]) = "Revenue" &&
    SELECTEDVALUE('Table'[GrandParentCategory]) = "Revenue",
    0, 1
)

This measure can be added as a visual-level filter in the matrix visual, set to show only values where Revenue_Filter equals 1. Each approach ensures that the revenue category is displayed only once, while other categories, such as Operating Expenses, remain fully detailed.

Best regards,

[Atl]

Hi @DataNinja777 , Thanks again for detailed reply. i tried below two methods but no luck. can you please try with my provided data in your power bi desktop and see? Best regards,

Revenue_Level_Flag = 
IF(
    'Table'[Subcategory] = "Revenue" && 
    'Table'[ParentCategory] = "Revenue" && 
    'Table'[GrandParentCategory] = "Revenue", 
    "Revenue (Keep Only One)", 
    'Table'[Subcategory]
)

Revenue_Filter = 
IF(
    SELECTEDVALUE('Table'[Subcategory]) = "Revenue" &&
    SELECTEDVALUE('Table'[ParentCategory]) = "Revenue" &&
    SELECTEDVALUE('Table'[GrandParentCategory]) = "Revenue",
    0, 1
)

[Atl]

Hi @DataNinja777 , Thanks. i will try out your logic and circle back with you. 

Best,