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.  

[Anonymous]

Hi @Atl 

There are design differences between Excel and Power BI, let me get as close as possible to the results you expect.

Create a measure.

DisplayRevenue = 
IF (
    SELECTEDVALUE('Table'[Subcategory]) = SELECTEDVALUE('Table'[ParentCategory]) &&
    SELECTEDVALUE('Table'[ParentCategory]) = SELECTEDVALUE('Table'[GrandParentCategory]),
    IF(
        ISINSCOPE('Table'[GLCATEGORY]),
        SUM('Table'[SSGLACCOUNTIDOTHER]),
        BLANK()
    ),
    SUM('Table'[SSGLACCOUNTIDOTHER])
)

Here is the result.

If this does not meet your requirements, I suggest you adjust the table structure in Excel and then import it into Power BI Desktop for use.

Regards,

Nono Chen

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 trying but i think you misunderstood. please review my post and screenshots again. Best,

[Atl]

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

[Atl]