Forum Discussion

Chennakesava458's avatar
Chennakesava458
Frequent Visitor
2 years ago
Solved

Custom Order in hierachical matrix visual

Hi Team,
 I have a requirment like 

we have 5 hierarchy levels
lower level need to show first then next level related records below is the sample data 

Can anybody please help on this..


 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Chennakesava458 ,

    Base on my research, there is no option to set the row columns in reverse order. There is an workaround can be achieve the similar requirement. You can find the details in the attachment.

    1. Create a sort order table

    RowName SortOrder
    employee1 1
    employee2 2
    Manager1 3
    employee2 4
    employee4 5
    Manager2 6
    Client1 7

    2. Create a measure as below 

    Measure = var _gl=SELECTEDVALUE('Sort Order'[RowName])
RETURN  CALCULATE(SUM('Table'[Sales]),'Table'[GL-4]=_gl||'Table'[GL-3]=_gl||'Table'[GL-2]=_gl)

    Best Regards

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Chennakesava458 ,

    You can refer the following blog to sort the column by custom:

    Sort a Column with a Custom Order in Power BI - RADACAD

    1. Create a customized sort order dimension table

    Quarter Year Order
    Q2 2024 1
    Q1 2024 2
    Q4 2023 3
    Q3 2023 4
    ... ...

    2. Combining the Sort Order Table with 'Dimension' table

    3. Sort by column

    If the above one can't help you figure out, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

    How to provide sample data in the Power BI Forum

     

    And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    • Chennakesava458's avatar
      Chennakesava458
      Frequent Visitor
      2 years ago

      Hi Team,
          Based on  below sample data 

      GL-2              GL-3          GL-4     Sales

      employee1Manager1Client11500
      employee2Manager2Client12500
      employee2Manager1Client14000
      employee4Manager2Client16800

       

      if we create Matrix visual 

      But my requiremnt is 

      employee1    1500
      employee2    4000
      Manager1     5500
      employee2    2500
      employee4   6800
      Manager2    9300
      Client1        14800

      Anybody Suggest how to achieve this

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Chennakesava458 ,

        Base on my research, there is no option to set the row columns in reverse order. There is an workaround can be achieve the similar requirement. You can find the details in the attachment.

        1. Create a sort order table

        RowName SortOrder
        employee1 1
        employee2 2
        Manager1 3
        employee2 4
        employee4 5
        Manager2 6
        Client1 7

        2. Create a measure as below 

        Measure = var _gl=SELECTEDVALUE('Sort Order'[RowName])
RETURN  CALCULATE(SUM('Table'[Sales]),'Table'[GL-4]=_gl||'Table'[GL-3]=_gl||'Table'[GL-2]=_gl)

        Best Regards