Reply
Member
Posts: 81
Registered: ‎10-18-2016

Column Secondary Sort Order

[ Edited ]

I have a bunch of orders, each with a status of Won, Lost or Open. All of these orders are divided by region. So I throw them all up on a Clustered Column chart, sorting by region. Great - all works. Except that for each region, the order of the columns within the region is alpha - Lost, Open, Won. I need it to be Won, Open, Lost.

 

So I created a new column - Status Order - and populated it with:

 

Status Order = IF('Order'[Status] = "Won",
                  1,
                  IF('Order'[Status] = "Open",
                     2,
                     3))

and used the Sort by Column item in the Modelling tab to sort by that column rather than by the Status column, which it was sorting by.

 

It didn't work - made no difference at all to the display.

 

What am I doing wrong? Or not doing?

 

Thanks in advance.

Super User
Posts: 444
Registered: ‎07-12-2017

Re: Column Secondary Sort Order

Rather than make a new column you could just create a new table saying Won 1, Open 2, Lost 3 and then link it, then try to sort by that column?

Member
Posts: 81
Registered: ‎10-18-2016

Re: Column Secondary Sort Order

I'm not sure how/whether that would work - my data source is actually one very long SQL statement pulling in five or six tables from the database.

Super User
Posts: 685
Registered: ‎11-01-2017

Re: Column Secondary Sort Order

"

and used the Sort by Column item in the Modelling tab to sort by that column rather than by the Status column, which it was sorting by.

 

It didn't work - made no difference at all to the display."

 

- Could you please post the snapshot of how you do this sorting?

 

Thanks

Raj

Member
Posts: 81
Registered: ‎10-18-2016

Re: Column Secondary Sort Order

[ Edited ]

See below

Image3.jpg

 

Member
Posts: 54
Registered: ‎05-02-2016

Re: Column Secondary Sort Order

try this, 

 

Drag drop the "Status Order" Column into the tooltip section of the chart and sort the chart on this "Status Order"

Member
Posts: 81
Registered: ‎10-18-2016

Re: Column Secondary Sort Order

That loses me the initial sort, by region.

 

It also puts the Status Order column in the tooltip, where I don't want it.

Highlighted
Super User
Posts: 685
Registered: ‎11-01-2017

Re: Column Secondary Sort Order

Hi @Budfudder 

 

How are you sorting this in modelling?

 

Try this way:

 

In fields  Panel-> go to the table-> select Status column, Now go to Modelling tab-> Sort by Status order column.

 

Please confirm does it solve the problem.

 

Thanks
Raj