Forum Discussion
Sorting by size
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
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.
22 Replies
Are you using a separate table for Company Size, or is it a column in your fact table? If you're using a separate table, you can add an Index (sort) column and then sort the Size column by the Index column:
If you're using a column in your fact table, you can create a calculated column with IF or SWITCH to assign the Index (sort) column, and then sort the Size column by the Index column as shown above.
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
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.
Since it's a text column, embed the values in double quotes:
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 )
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
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
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.
Hi Jianbo Li,
Putting the index into the tooltips section worked for me. The visuals you sent were also extremely helpful.
Thank you!
Chris
Also thank you DataInsights for all of your help thus far!
Chris
