Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Please check and provide possible solutios.thanks in advance for your help.
Thanks,
Mahi1827
Solved! Go to Solution.
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)
Another table with actual values (Say Table 2)
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
2.) Expand the merge column and sort ascending and this will add a new column in Table 2, something like this:
3.) In your PBI Visual- please use Static.Custom (New merged Column) as your Axis
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
Hope this helps. 🙂
Cheers,
-Namish B
Hi NamishB,
Thank you so much for your time and help...your approach is worked well..
Thanks,
Mahi1827
Hi @Mahi1827
You can try Adding SortOrder Column in Tooltip and with three dots can sort by the column (ascending)
Output will be
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
sort order table
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.
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)
Another table with actual values (Say Table 2)
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
2.) Expand the merge column and sort ascending and this will add a new column in Table 2, something like this:
3.) In your PBI Visual- please use Static.Custom (New merged Column) as your Axis
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
Hope this helps. 🙂
Cheers,
-Namish B
Hi NamishB,
Thank you so much for your time and help...your approach is worked well..
Thanks,
Mahi1827
@Mahi1827 , if the difference is a column you can use that on axis and use sort option under three dots (...)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |