Forum Discussion

BojepJoe's avatar
BojepJoe
New Member
1 year ago
Solved

Multilevel Custom Sorting Clustered Column Chart

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 exa...
Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    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.

alish_b's avatar
alish_b
Super User
1 year ago

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#M1426411


Hope it helps!