How to sort based on multiple columns

[afleiderman1]

I am wondering how would do the following 

I have the followign table

SERIAL     Company         Product              Amount 

ABC            XYZ                 WATER               91

FAK             C2K                 FIRE                    21

ABC             XYZ                 ICE                15

K2A              ARKK                    EARTH              21

west              arkk                  ice                      12

DDD         ALP                    earth                 120

I would like to sort this first by the amount and then by the serial number

to have something like

WEST ARKK      ICE          12

FAK     C2K     FIRE          21

ABC      XYZ     WATER              91

ABC      XYZ        ICE          15

DDD    ALP         EARTH    105

Basically I want to sort by both the amoutn and the serial number. 

I am using the data visual table in power bi

[Anonymous · Accepted solution]

Hi @afleiderman1 ,

I have created a simple sample, please refer to it to see if it helps you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRioiMApLhjiGuQUDa0lApVidayc3RG8hxNgKRbp5BrkDKCCKDrMnTGSRhaAqW8DZyBHIcg7xBelwdg0I8EJrKU4tLgLzEouxshDYjsJSLiwtIm08Aki5DIwOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [serial = _t, COMPANY = _t, PRODUCT = _t, AMOUNT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"serial", type text}, {"COMPANY", type text}, {"PRODUCT", type text}, {"AMOUNT", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "serial", "serial - Copy"),
    #"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "serial - Copy", Splitter.SplitTextByRepeatedLengths(1), {"serial - Copy.1", "serial - Copy.2", "serial - Copy.3", "serial - Copy.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"serial - Copy.1", type text}, {"serial - Copy.2", type text}, {"serial - Copy.3", type text}, {"serial - Copy.4", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"serial - Copy.2", "serial - Copy.3", "serial - Copy.4"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom", each if [#"serial - Copy.1"] = "A" then 26 else if [#"serial - Copy.1"] = "B" then 25 else if [#"serial - Copy.1"] = "C" then 24 else if [#"serial - Copy.1"] = "D" then 23 else if [#"serial - Copy.1"] = "E" then 22 else if [#"serial - Copy.1"] = "F" then 21 else if [#"serial - Copy.1"] = "G" then 20 else if [#"serial - Copy.1"] = "H" then 19 else if [#"serial - Copy.1"] = "I" then 18 else if [#"serial - Copy.1"] = "J" then 17 else if [#"serial - Copy.1"] = "K" then 16 else if [#"serial - Copy.1"] = "L" then 15 else if [#"serial - Copy.1"] = "M" then 14 else if [#"serial - Copy.1"] = "N" then 13 else if [#"serial - Copy.1"] = "O" then 12 else if [#"serial - Copy.1"] = "P" then 11 else if [#"serial - Copy.1"] = "Q" then 10 else if [#"serial - Copy.1"] = "R" then 9 else if [#"serial - Copy.1"] = "S" then 8 else if [#"serial - Copy.1"] = "T" then 7 else if [#"serial - Copy.1"] = "U" then 6 else if [#"serial - Copy.1"] = "V" then 5 else if [#"serial - Copy.1"] = "W" then 4 else if [#"serial - Copy.1"] = "X" then 3 else if [#"serial - Copy.1"] = "Y" then 2 else if [#"serial - Copy.1"] = "Z" then 1 else 0)
in
    #"Added Conditional Column"

Then create measures.

Measure =MAX('Table'[AMOUNT])*100+MAX('Table'[Custom])

rankx = RANKX(ALL('Table'),[Measure],,ASC,Dense)

If I have misunderstood your meaning, Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[vicky_]

Check if you can try some of the suggestions in this thread: https://community.powerbi.com/t5/Desktop/Sorting-a-table-using-multiple-columns/m-p/2315317#M837166

[afleiderman1]

Yeah I tried the shift key and multiple column but I cannot get it too look the way I want