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.
Hello folks,
I am struggling to use an Excel Pivot Table function, like the one you pick a label and simply drag to the left or right in order to change the presentation of a Pie Chart (or any other chart). Let me enphasize that I DO NOT wish to order ascending or descending, because the labels are "TEXTS", so it doesn´t matter what they are, but there is an order I wish to follow. For exaple, period "1-30 days" first, then "30-60 days", then "60-120 days" and finally "Above 120 days".
I cannot think that it is not part of Power BI structure, so please help!
Example:
Solved! Go to Solution.
It is important that you use "Sort by column" from the upper menu after you have selected the Category column. From the options you will see, select the Order column.
It is not the same as just ordering a table by a column, it is ordering a column by another column.
Michael
Hi @germanobasler,
You problem has been resolved? If you have resolve, please mark the right replay as answer, or welcome to share your solution. Other people will find workaround clearly. If it hasn't, please feel free to ask any issue.
Best Regards,
Angelia
Hello Angelia, thanks for asking.
Unfortunately my problem has not been resolved. I really tried the suggestions, either way it did not succeeded. My database is composed by thousands of lines, which are summarized in this pie chart. Not sure that these solutions apply for such a database.
Moreover, these solutions are far more complicated than it should be to solve this simple thing with almighty MS Power BI.
Still waiting for a step-by-step solution to this pie chart question.
What sort options do you have in the visual options in the top right hand corner of the visual?
Hello Phil, thanks for replying.
Here are the options:
But I do not wish to "Sort" in any way, I wish to change the order manually, like we can do with automated "Pivot-Table Graphs". Basically, for it to work "sorting", I´d have to name the categories in a determined "sortable" way. Sometimes it is not possible. And turning back to the database in Power BI is not the the right move with this great tool.
One option is to create measures for each wedge of your pie and then drag multiple measures
eg.
Measure A = 10 Measure B = 20 Measure C = 5
And then you can drag the three measures to the Values area where you can control the order. You can replace my hardcoded values with your actual calculations.
Thanks for your feedback.
Am I not loosing the label of the real data presented in the pie chart wedges?
I didn´t figure it out the way it really works, so I still have this scrambled wedges in my pie chart.
Regards,
GB
You can achieve this using "Sort by Column" under Modeling menu.
See the difference - one chart is sorted by ABC automaticaly
The other one - using "Sort by column".
In your "CategoriesDim" table add an Order column and sort the Category column by it.
Your database is very short. Do you think it works for thousand-long databases, where each of the pie chart slices have more than 500 lines and the size of the slices represent the sum ou count of those hundreds of lines?
@germanobasler did you try "Sort by Column" option as I have proposed?
It's exactly what you need.
It doesn't matter that your DB is thousands of rows. As long as you have a limited number of stages.
You should create a StageDim table with few entries as the number of stages.
Then connect it to your DB Fact table
Then, define the Order column it the small dimension table
Then, use "Sort by column" option as I have described previously.
It should easily work
Michael
Dear @Anonymous, good afternoon. I tried to proceed as you advised but still didn´t succeed.
I am trying to post printscreens, but it won´t let me!
I created a new sheet with only two columns (Categories to the left and Order on the right from 1 to 7). And then I sorted column "ascending" from 1 to 7, then the categories appeared in the right way.
Sheet uploaded. Then PBI already understood the relation between this sheet and the main database. Link was created automatically in Relationships tab.
Then back to Report tab, I replaced in the pie chart the column "Category" from the main database with the brand new created sheet ordered "category". The values to appear in the pie chart I pulled from the main database, as it is linked.
Result: Same thing. I must have missed something.
@germanobasler I think You have missed one step:" Sort by column":
Open the new small table (on your left side there are three icons, chose the middle one that looks like a table, then, on your right side click on the new table)
Select the Stage column, then in the upper menu click "Sort by column" and chose the Order column.
Please see screen shots that I have posted earlier.
This step tells the system that you want to sort the values in one column "Stage" by the values in another column "Order"
Together with all the rest that you have done it should work
Michael
It is important that you use "Sort by column" from the upper menu after you have selected the Category column. From the options you will see, select the Order column.
It is not the same as just ordering a table by a column, it is ordering a column by another column.
Michael
@Anonymous
Yes, you got my point and that was precisely what I missed, I missunderstood the steps.
It works! Thank you a lot.
Thanks a lot to all folks that answered as well.
My best regards,
Germano Basler
Glad to hear it worked!
May I also suggest that instead of using Pie Charts - try vertical Bar charts or a Tunnel chart.
Usually, it is not quite effective to show information of more than two categories on a Pie chart: it's hard to tell the relativeness of each slice.
Compare for example these three charts showing the same data.
From the Pie chart it is hard to say which category is bigger: Apples or Bananas. As well as Candy vs Drones vs Elephants
Whereas the proportions are very clear on the other two charts.
You can also sort those by the Category.
Good luck!
Michael Shparber
Michael, I cannot thank enough. You are absolutely right.
At the end, it was really worthwhile to learn the function "Sort Column", which widened my PBI knowledge! (still a loooong way to go =D )
Germano Basler
My pleasure!
Good luck with PBI! It is AWESOME!
Enjoy and someday you will be helping others in this community also!
Cheers,
Michael
Hi @germanobasler,
I personally suggest you handle your resource data in database, then load the result table to Power BI to visual them.
Best Reegards,
Angelia
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |