cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

How to sort chart axis values from negative to positive order

Hi,

I have 3 columns status, diffrence , total records like below

status             diffrence       total records
open              -10                  10
close               -9                  20
inprogress      -4                  30
completed     +15                40
reopen           +25               50

 

i would like sort above data in chart with axis as "diffrence" column from negative to postive .

I have tired by creating new table for custom sort and tried sort by new column sortorder from 1 to 5 but the sort is only working for table visual.

 

status                diffrence         totalrecords     sortorder
open                  -10                      10                        1
close                   -9                      20                         2
inprogress          -4                       30                         3
completed          +15                    40                         4
reopen                +25                    50                         5

 

when i used or transform above table data into any chart  or used in any new chart the axis that is "diffrence" scale then sort order from negaive to postitive changed..and not able to get expected sort order in chart.


so in axis i would like sort from negative to postive with respect to status and total records. like below.

1.png

 

Please check and provide possible solutios.thanks in advance for your help.

 

Thanks,
Mahi1827

2 ACCEPTED SOLUTIONS

HI @Mahi1827 - So what i understood is you have basically 2 tables-

One is a static table with -18+ as sort Order 1 and soo on.  (Say Table 1)

NamishB_1-1603918287041.png

 

 

Another table with actual values (Say Table 2)

NamishB_0-1603918258930.png

 

You want to Sort the bar graph of table 2 based on Table 1 order. if this is correct understanding you can follow the below steps:

1.)  Merge both tables on Scale column, Bring Custom column in Table 2

NamishB_2-1603918923852.png

 

2.) Expand the merge column and sort ascending and this will add a new column in Table 2, something like this:

NamishB_3-1603919011782.png

 

3.) In your PBI Visual- please use Static.Custom (New merged Column) as your Axis

NamishB_4-1603919162528.png

sort with the help of three dots on this visual and it will show in desired output

 

Extra Tip: If you want to show actual Value(numbers) in Bar Graph, just turn on the data labels and remove X Axis to get rid of 1,2,3. Something like this

 

NamishB_5-1603919392680.png

 

Hope this helps. 🙂

 

Cheers,

-Namish B

 

View solution in original post

Hi NamishB,

 

Thank you so much for your time and help...your approach is worked well..

 

Thanks,

Mahi1827

View solution in original post

8 REPLIES 8
Post Prodigy
Post Prodigy

Hi @Mahi1827 

You can try Adding SortOrder Column in Tooltip and with three dots can sort by the column (ascending)

Output will be 

Capturea.PNG

 

Is this what you are after?

 

Thanks,

-Namish B

Hi NamishB,

Thanks for your time.

 

I have tried your approach still ididnt get values in expected order.

here is my requirement and also check sample pbix file in below link

data summary table

1.png

sort order table

 

2.png

when i use these details in table view with sort order ...data is sorted from negative to positive values like from -18+ to 54 end.

3.png

 

 

But i need same details in chart view i should take scale is axis, status is legend and value is recorddata(value in table), and finally taken sort in tooltip.

 

 in chart, the scale order is not accurate the scale of axis in chart it should consider from

-18+,-18,-15,1,24,54.  here sample file chart have -18+ is shown in middel of the scale but it should shown  as begining of the chart.

 

so please help me on any possible solution to get accurate scale details from detail negative values to postive sort order.

 

Pease check sample pbix  and povide possble solutions.

https://onedrive.live.com/?cid=9E9277B1AE261150&id=9E9277B1AE261150%21119&parId=root&o=OneUp

 

Thanks for your help  in advance.

 

Thanks,

Mahi1827

Hi @Mahi1827 - on what basis -18+ should be shown as 1?

Lets leave about order for now, even If we try to auto arrange Scale column based on ascending it will be -15, -18, -18+, -18+, 1 etc etc. Hence you see -18+ shown in middle. There is no way -18+ will be at top. Unless this is static data and wont change. Please advice

 

Also Scale column is having data type as Text?

 

Cheers,

-Namish B

Hi NamishB,

Thanks for your time,

 

actually the scale column is constant...in overall data is the max scale value is -18+ so to get order of negative to postitive values..so sort order is 1 for -18+ (data is start from here)

 

so the status column count will change with respct to scale. for scale status count will change like for example in -18+ scale will get both open and closed requests and -15 scale will get only pending reqeusts like that . so when do sorting values from scale axis all  status and value counts should start from axis of -18+ and then -18, -15,-1,0 and 1 etc...

 

scale as text but it sorted from sort order (integer) in table level.

 

so please check if any workarounds to get scale in axis from -18+ ,-18,-15,-12,-1,0,1,+18 etc..

 

thanks in advance for your inputs and help.

 

Thanks,

mahi1827

HI @Mahi1827 - So what i understood is you have basically 2 tables-

One is a static table with -18+ as sort Order 1 and soo on.  (Say Table 1)

NamishB_1-1603918287041.png

 

 

Another table with actual values (Say Table 2)

NamishB_0-1603918258930.png

 

You want to Sort the bar graph of table 2 based on Table 1 order. if this is correct understanding you can follow the below steps:

1.)  Merge both tables on Scale column, Bring Custom column in Table 2

NamishB_2-1603918923852.png

 

2.) Expand the merge column and sort ascending and this will add a new column in Table 2, something like this:

NamishB_3-1603919011782.png

 

3.) In your PBI Visual- please use Static.Custom (New merged Column) as your Axis

NamishB_4-1603919162528.png

sort with the help of three dots on this visual and it will show in desired output

 

Extra Tip: If you want to show actual Value(numbers) in Bar Graph, just turn on the data labels and remove X Axis to get rid of 1,2,3. Something like this

 

NamishB_5-1603919392680.png

 

Hope this helps. 🙂

 

Cheers,

-Namish B

 

View solution in original post

Hi @Mahi1827 - Does this solved the purpose? 

 

Thanks,

Namish

Hi NamishB,

 

Thank you so much for your time and help...your approach is worked well..

 

Thanks,

Mahi1827

View solution in original post

Super User IV
Super User IV

@Mahi1827 , if the difference is a column you can use that on axis and use sort option under three dots (...)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors