Removing Blanks from Matrix with Ragged Hierarchy

[Anonymous]

Hi,

I have a ragged employee hierarchy with different lengths that looks like the below:

I also have a list of Account Names and Account Owners. I also have total spend for each of the accounts.

I've built a matrix that sums up each of the spends with account by salesperson, and also incorporates the hierarchy information. However, since the rows of the hierarchy are different lengths, I end up with this clunky looking matrix filled with blanks because that level of the hierarchy is blank.

How do I get rid of the blanks so when I, for example, expand Hilary I see "Account G" right under her name rather than the multiple blanks. I've included my sample dashboard below, any help would be greatly appreciated, thank you!

Sample Dashboard: https://www.dropbox.com/s/qsqr1o8a0pbsty6/Ragged%20Hierarchy%20Dummy.pbix?dl=0

[vishnumohanout]

Hi there,

I was able to work out a solution, for a similar issue I had, through a custom measure & some DAX. It worked for matrix visualization but creates a small performance drop. I didn't have your table details so I used some fillers instead. Make sure to use only the newly created measure, remove the old one. Good luck.

No_Null_Total_Spend = 
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Table_Name'[Name.Level 01] )
        && ISBLANK ( SELECTEDVALUE ( 'Table_Name'[Name.Level 01] ) ), BLANK (),
    ISINSCOPE ( 'Table_Name'[Name.Level 02] )
        && ISBLANK ( SELECTEDVALUE ( 'Table_Name'[Name.Level 02] ) ), BLANK (),
    ISINSCOPE ( 'Table_Name'[Name.Level 03] )
        && ISBLANK ( SELECTEDVALUE ( 'Table_Name'[Name.Level 03] ) ), BLANK (),
    ISINSCOPE ( 'Table_Name'[Name.Level 04] )
        && ISBLANK ( SELECTEDVALUE ( 'Table_Name'[Name.Level 04] ) ), BLANK (),
    SUM ( 'Table_Name'[Total_Spend] )
)

[Pragati11]

Hi @Anonymous ,

As a best practices from data point of view, I suggest you clean your BLANK values. Replace Blanks with some text value.

Thanks,

Pragati

[Anonymous]

This would also look silly as there would be many repeat values. Was hoping for a workaround so I could just expand the first Hilary only.

[sevenhills]

Based on the link below, it is not possible in the default Matrix visual (with blanks in the data).
The link suggests some alternatives to consider (like no-stepped layout, custom visual ... )

https://radacad.com/removing-blanks-from-organizational-ragged-hierarchy-in-power-bi-matrix-visual