Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
germanobasler
Helper I
Helper I

Pie Chart Order of Slices (NOT accordingly to letters of numbers order)

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:

Power BI pie chart.JPG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

17 REPLIES 17
v-huizhn-msft
Employee
Employee

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.

Phil_Seamark
Employee
Employee

Hi @germanobasler

 

What sort options do you have in the visual options in the top right hand corner of the visual?

 

sort by pie.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hello Phil, thanks for replying.

 

Here are the options:

pie chart reply.JPG

 

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.

 

pie sorting.png

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Anonymous
Not applicable

@germanobasler

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.Sort.JPG

 

 

Pies.JPG

 

 

 

 

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?

Anonymous
Not applicable

@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.

 

 

Anonymous
Not applicable

@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

Anonymous
Not applicable

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

Anonymous
Not applicable

@germanobasler 

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

NoPie.JPG

 

 

 

 

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

 

 

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.