Multilevel Custom Sorting Clustered Column Chart

[BojepJoe]

Hi, I have a chart with drill down for each category. I want to sort the x axis on each level based on my own custom order.

For example this milk category have different size that I want to sort exactly like this

1000TP

1000CB3

750CB3

250TP

200TP

125TP

and the next level of drill down for example 125TP I need to custom sort as well.

[v-pgoloju]

Hi @BojepJoe,

Just following up to see if the Responses provided by community members were helpful in addressing the issue. If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @BojepJoe,

Just following up to see if the Response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @BojepJoe,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @rohit1991 and @alish_b  for prompt and helpful responses.

Just following up to see if the Responses provided by community members were helpful in addressing the issue. If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[alish_b]

Hey @BojepJoe ,

If I understood your requirement right, you want a custom sorting at each level of drill-down, that is, at Category Level you will have one sorting logic and then at SubCategory level you will have a completely different sorting logic. If that is the case, then that sadly is not supported in Power BI at present. You could only either sort by any one of fields in the X-axis or by the numerical value presented in the bar.

I believe there was a similar discussion in this thread: https://community.fabric.microsoft.com/t5/Desktop/Order-of-columns-when-drilling-down/m-p/4790971#M1...

Hope it helps!

[rohit1991 · Accepted solution]

Hi @BojepJoe 

To get multilevel custom sorting in a clustered column chart:

1. Create Sort Tables

   • One for Category with your custom order (Category, SortKey_Category).

   • One for Subcategory with order inside each category (Subcategory, SortKey_Sub).

2. Model Setup

   • Relate Fact[Category] <<CategoryOrder[Category].

   • Relate Fact[CatSubKey] << SubcategoryOrder[CatSubKey].

3. Sort by Column

   • In Model view:

     • Category<<Sort by SortKey_Category.

     • Subcategory<<Sort by SortKey_Sub.

4. Hierarchy & Visual

   • Build a hierarchy: Category<<Subcategory.

   • Place it on the X-axis of your clustered column chart.

   • Enable Drill Down to move between levels.