How to fix stacked bar chart showing blanks in the lowest level of drill-down?

[Anonymous]

Hi,

I am creating a drill-down stacked bar chart that drills down on different organizational levels from highest to lowest. One part of our business has even a lower level of hierarchy that the other parts of the company don´t have. My problem is that if I add that level to the hierarchy that I have created, all the other rows show a blank, as they don´t have that level of hierarchy included in it. I can´t change it in the visualization, and if I delete the blank in the visualization of the lowest level, then it obviously removes all the rows that don´t belong to that lowest level of organizational hierarchy. Is there a workaround for this?

Any help would be highly appreciated! Thank you already in advance.

[Anonymous]

Hi @Anonymous ,

To manage the visualization without the blanks appearing for missing hierarchy levels, you can consider the following workaround:

1.Use of DAX to Create a Unified Hierarchy Level: Create a new column in your data model using DAX that incorporates a placeholder for the missing hierarchy levels. This placeholder can be a specific value like "N/A" or "Not Applicable". This way, every row will have a value for each level of the hierarchy, preventing blanks in your visualization.

NewHierarchyLevel = IF(ISBLANK([YourHierarchyColumn]), "N/A", [YourHierarchyColumn])

2.Adjust the Visualization: Use this new column as your hierarchy in the drill-down stacked bar chart. This ensures that all parts of your business are represented, even if they do not have data for the lowest level of the hierarchy.

Best Regards,

Neeko Tang

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

[Anonymous]

Hi Neeko,

I really appreciate your quick reply, thank you for that. But doesn´t this just change the blanks to "N/A" and then the visualization on the lowest level still shows the N/A values? Before they were just blank and they still showed up on the lowest level of my visualization. Maybe I explained my problem incorrectly, but I need the values that are blank or N/A gone from the lowest level, but to show on the upper levels.

Kind regards,
UV

[Anonymous · Accepted solution]

Hi @Anonymous ,

Unfortunately, the current system does not support this function.

If you would like to suggest  feature improvements, you may vote the idea and comment here to improve this feature. It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

Best Regards,

Neeko Tang

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

[Anonymous]

Then I have to just rename the blanks to something that is understandable for the end-user. Thank you for taking the time to reply to me, I really appreciate it. I will suggest this as a future improvement when I have time at the end of my workday. I wish you a great week! 🙂

Kind regards,
UV