how to not break bars order with removing "order" field

[Anonymous]

I have a bar chart displaying value over the period (3 months buckets from youngest to oldest)

data looks like that:

Sorting the chart didn't help, so I added column age-order so I can display it the way I need (to display values from "less than 3 months" to "older than 60 months"), I was only able to use "age-order" in the tooltip but of cause it shows in the tooltip

so the question is: are there other options? I don't want the end user to see it in the tooltip

Thank you

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

In your scenario, we should insert an age column in power query and make the table sorted by the custom column.We can split the Age column firstly, then we can insert a custom column by the splited columns and remove the unnecessary columns. Here is the M code for your reference.

l

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc87CsAgEEXRvUxt4S9G1yIiQgQLoxDdP4EQmGHqA493Y4Re18q7lZFNvufYbYEABUlQckiaUUAyjJRGs9w82sFMWzTHzJArJzNLNj0vkGjhs9mv+hD8yyWk9AI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Age = _t, values = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Age", type text}, {"values", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Age", "Age - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Age", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Age.1", "Age.2", "Age.3", "Age.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Age.1", type text}, {"Age.2", type text}, {"Age.3", Int64.Type}, {"Age.4", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Age.2", "Age.4"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if [Age.1]="less" then [Age.3] else if [Age.1] = "older" then [Age.3]+1 else [Age.3]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Age.1", "Age.3"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Custom", Order.Ascending}})
in
#"Sorted Rows"

Then we can get the result as we excepted.

PBIX as attached.

Regards,
Frank

[acbg]

@Anonymous within Data select Age column and go to Modeling tab (at the top) and go to Sort by Column and sort by age_order.

This should sort your Age column in the graph now automatically without needing to do sort by.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

In your scenario, we should insert an age column in power query and make the table sorted by the custom column.We can split the Age column firstly, then we can insert a custom column by the splited columns and remove the unnecessary columns. Here is the M code for your reference.

l

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc87CsAgEEXRvUxt4S9G1yIiQgQLoxDdP4EQmGHqA493Y4Re18q7lZFNvufYbYEABUlQckiaUUAyjJRGs9w82sFMWzTHzJArJzNLNj0vkGjhs9mv+hD8yyWk9AI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Age = _t, values = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Age", type text}, {"values", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Age", "Age - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Age", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Age.1", "Age.2", "Age.3", "Age.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Age.1", type text}, {"Age.2", type text}, {"Age.3", Int64.Type}, {"Age.4", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Age.2", "Age.4"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if [Age.1]="less" then [Age.3] else if [Age.1] = "older" then [Age.3]+1 else [Age.3]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Age.1", "Age.3"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Custom", Order.Ascending}})
in
#"Sorted Rows"

Then we can get the result as we excepted.

PBIX as attached.

Regards,
Frank

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank

[Anonymous]

@acbg sorry I meant age-order column (updated desc)

when I follow that the "Sort by column" use Age by default if I switch to age_order (the one that I need it  to be sorted by) I receive an error

end removing age-order from the tooltip breaks the order