Sorting by size

[ChrisDg]

Hi,

I'm new to Power BI and I'm having difficulty sorting my x-axis for this column chart. This is what I'm seeing: 

I don't see any other sorting options. When I click on sort descending, from sort ascending, the x-axis doesn't change. Any ideas?

Thank you,

Chris 

[ChrisDg]

Also thank you DataInsights for all of your help thus far!

Chris 

[v-jianboli-msft · Accepted solution]

Hi @ChrisDg ,

Just as @DataInsights  mentioned before, you can create an Index column, then sort it by index.

Except the methods @DataInsights  mentioned before, you can also add Index columns in PQ:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYxBCoAwDAS/IrnaQjZtUV/gB7wVD4JHURD/j0msx5mdpFYCBRJaQyWJYAUMTkAshmVyLIjCPrdd2AIz4yeKCrAb+W6UvPE3aFGrzCX8GaNXztl5vrdz75br2Q6TSd+vLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company size" = _t, #"Total Views" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company size", type text}, {"Total Views", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Index"}),
    #"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type)
in
    #"Added Index1"

Then sort column by index:

Output:

Here is another method to achieve your goal:

Add Index to the tooltips field:

Then sort it:

Best Regards,

Jianbo Li

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

[ChrisDg]

Hi Jianbo Li,

Putting the index into the tooltips section worked for me. The visuals you sent were also extremely helpful. 

Thank you!

Chris 

[ChrisDg]

Hi DataInsights,

Is this what you are referring to? I keep getting an error message: 

I also get an error message if I take out the column = 

Thank you,

Chris

[DataInsights]

@ChrisDg,

You have to use your table name, not "Table1" (second screenshot in your reply).

[ChrisDg]

Hi DataInsights,

Thank you for clarifying. I've updated the Table name to my Table name. However, when I sort it by the Index column it is sorting by the numbers given to the Index column. If I remove my Index column from my visualization then it goes back to sorting the original way. How do I get it to sort with the way that is shown in the visualization without showing the numbers given to the Index column?

Thank you,

Chris 

[DataInsights]

Don't add the Index column to the visual. The Index column is used internally for sorting. If you configure your visual to match the screenshot in your original post, it should work.

[ChrisDg]

Hi Data Insights,

I've unchecked the index column from my visualization, but it still isn't changing the sorting: 

Thank you,

Chris 

[DataInsights]

Disable sorting in the visual. There's no need to sort in the visual since you've already defined the sort order via the Index column in Data view.

[ChrisDg]

Hi DataInsights,

How do you disable sorting in the visual?

Thank you,

Chris 

[DataInsights]

In the visual, click the ellipsis. Navigate to Sort axis, and select Company Size. This will toggle off the sort.

[ChrisDg]

Hi DataInsights,

It looks the same whether I select the ascending or descending sort option.

Thank you,

Chris Degler

[DataInsights]

You shouldn't be using Ascending or Descending. Disable the sort completely by removing the check mark from "Company Size" in Sort Axis. It's a toggle that you can select/deselect.

[ChrisDg]

Hi DataInsights,

Where do you go to disable the sort completely? Where is the sort axis located?

Thank you,

Chris 

[DataInsights]

[ChrisDg]

Hi DataInsights,

For the company size option in the sort axis section, I don't see an option to disable this by removing the check mark. For example, if I click on company size (while it is check marked) it will automatically go to the other selection option which is sort by total views. If I right click on this option it doesn't do anything. Can you clarify what your recommendations are?

Thank you,

Chris 

[DataInsights]

Try removing the Company Size field from the visual and then add it back.

[ChrisDg]

Hi DataInsights,

I tried that and it didn't work.

Thank you,

Chris 

[ChrisDg]

Hi DataInsights,

Thank you for your reply. I found the screen that you are using, but I'm confused on how you would add the IF or SWITCH function. I'm also confused on what the formula would look like. Here is what I currently see: 

Thank you,

Chris 

[DataInsights]

@ChrisDg,

Add this calculated column to the table in your screenshot (change Table1 to your table name):

Index =
SWITCH (
    Table1[Company Size],
    1, 1,
    2 - 10, 2,
    11 - 50, 3,
    51 - 200, 4,
    201 - 500, 5,
    501 - 1000, 6,
    1001 - 5000, 7,
    5001 - 10000, 8,
    9
)

Then, click the Sort by column button and select the Index column. This will sort Company Size by the Index column in visuals.