Keeping the hierarchy grouping in clustered columns

[Samuel_Tavern]

Hello,

I have a Pivot Table in Excel with a 2-level hierarchy. Imanually re-arranged the rows in the Pivot table in order to have the groups ordered by the sum of values of the first level, and then ordered by the values of the second order:

I have the same data now available in Power BI and would like to recreate this chart. I have tried two approaches, both with non-satisfactory results:

• Use the hierarchy on the X-axis and sort by count. The x-axis is then sorted by the values of the "second level", without taking into account the total value of the "first level".
• Use small multiple to split the chart between the 3 categories of the first level. However for each small multiples all available vetagories of the second level are displayed, even if they do not contain any data. This made the chart very ugly.

What would be the way to display my data in the same way I did with the pivot table, without using to complicated DAX?

Here is the link to get the Power BI file with dummy data:

Sub Pareto Tests.pbix

And here is the link to get the wanted display with the same data, in Excel:

Sub Pareto Demo.xlsx

Thanks lot,
Samuel

[Ritaf1983]

Hi 
@Samuel_Tavern 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[Samuel_Tavern]

Hi Ritaf1983,
Thanks for your answer. I have added links to the OneDrive versions of the PowerBI file and an Excel file with the example of what I want in my initial post.

Best regards,

Samuel

[Ritaf1983 · Accepted solution]

Hi @Samuel_Tavern 

Unfortunately, there is no straightforward method for implementing nested sorting in charts.

It might be possible to achieve this using DAX.

I've attached a link to a discussion with a solution that was proposed a few months ago.

The discussion focuses on a cluster chart, but the underlying logic is applicable.

https://community.fabric.microsoft.com/t5/Desktop/Help-Needed-in-Nested-Sorting-for-Hierarchical-Bar...

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