Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Custom Sort bar chart

Hello,  

I'm new to Power BI and trying  to sort the chart below by the following order: Agree, Strongly Agree, Neither Agree Nor Disagree, Disagree, Strongly Disagree. I have other statements that I would like to present in this order as well. Please advise.  

 

Thank you

mc

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    Anonymous in Power Query add another field in your table. assuming your existing field is called "Status" and new column is called "Status Order"

     

    if [Status] = "Agree" then 1 else
if [Status] = "Strongly Agree" then 2 else
if [Status] = "Neither Agree Nor Disagree" then 3 else
if [Status] = "Disagree" then 4 else
5

    Close and apply the changes, go to modelling tab, select "status" field and choose sort by column option and select "Status Sort" column.

     

    Your data will sort as per your need.

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Anonymous in Power Query add another field in your table. assuming your existing field is called "Status" and new column is called "Status Order"

     

    if [Status] = "Agree" then 1 else
if [Status] = "Strongly Agree" then 2 else
if [Status] = "Neither Agree Nor Disagree" then 3 else
if [Status] = "Disagree" then 4 else
5

    Close and apply the changes, go to modelling tab, select "status" field and choose sort by column option and select "Status Sort" column.

     

    Your data will sort as per your need.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you! I'll try. Appreciate the quick response.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello,

      I'm unclear on the steps below (new Power BI user:smileyembarrassed:). Below is a snapshot of my data.  Will I need to create multiple status order columns for each statement?

      Thank you for your help,

      mc

      • v-cherch-msft's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous 

        Please check the two tables in attached file.They show two ways for you to order it.

        First,create order columns in query editor for each Question column and sort by them.(Table1,Page1)

        Second,unpivot your table first.Then create one sort column in query editor and sort by it.(Table2,Page2)

        Regards,

         

    • saraammain's avatar
      saraammain
      New Member
      3 years ago

      Hi, when I do that, I get a circular dependency error when I try to sort. Could that be because I created the new order column in Power BI desktop instead of going to Power Query?

    • saraammain's avatar
      saraammain
      New Member
      3 years ago

      Thanks, worked now 🙂 However, in order not to add numerous additional columns to my data table, in the end I created separate "sorting tables" (with two columns: names for the x-axis and index), connected them with my data table, used the new table for the x-axis and sorted the x-axis by the index column.