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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

How to display only the top 10 values in a chart

I have a pie chart with a dimension that has more than 100 values and I want to present this chart only the top 10 in ascending order

 

 

Untitled2.png

2 ACCEPTED SOLUTIONS
Sean
Community Champion
Community Champion

@Anonymous  try this

 

Measure = RANKX (ALL(DMEGF_Fornecedor[Fornecedor]),  CALCULATE(SUM(Facts [Vl_PedidoCompra])))

View solution in original post

Sean
Community Champion
Community Champion

@Anonymous Select the chart and then - click the 3 dots in top right corner of the chart...

Sort Chart.png

 

View solution in original post

20 REPLIES 20
Anonymous
Not applicable

Updating this thread as there is a solution now that probably came with the latest updates. In the Visual level filters, if you click on the drop down arrow of the group that you want to show top N of, click on the drop-down for "Filter type" and you should be able to see the choice "Top N".

 

With that selected, you are now able to show "Top Items" and input your value (1,2,3,4,5 ...10). Finally, pull in the column where you are calculating the value into "By value" and you should be able to quickly get what you need.

Anonymous
Not applicable

i use this but many a times, it shows more than 10 values

dardo
Regular Visitor

Hi @Anonymous,

 

Just to to the visual filters of the chart. Then add a filter of the leyend type Top N in this case set 10. You need also to add the By value parameter to order this top 10.

I hope it works.

 

 

Greg_Deckler
Super User
Super User

To get your rank, you should be able to use the documentation for RANKX:

https://msdn.microsoft.com/en-us/library/gg492185.aspx

 

Or for EARLIER

https://msdn.microsoft.com/en-us/library/ee634551.aspx

 

 

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
teylyn
Advocate III
Advocate III

After you've sorted your top ten issue, please consider using a different chart type. Pie charts, specifically 3D pie charts are very bad choices for data visualisations. Humans cannot easily compare wedges of a slice, and a 3D distortion makes it even more difficult.

With two or three data points, a pie chart is fine, because the differences are still easy to see, but for more than that consider horizontal bar charts, sorted by value.

 

For a more comprehensive discussion about alternatives to pie charts please read Save the Pies for Dessert by Stephen Few. 

I too strongly dislike the Pie Chart.  Few's essay was a pleasure to read.  

 

Nonetheless I have to commend the Pie Chart for one additional arcane application: the agenda for a 1-hr meeting.  The similarity between a pie chart and the face of a clock lead to a quick and enjoyable appreciate of how the next hour will be spent.   A small slice at the beginning can even be devoted to slandering the Pie Chart and recommending a 'Few' alternatives.  

It's not so much about liking or disliking pie charts. Pie charts to serve a purpose and when used correctly, they can be of great value. Unfortunately, they are often used wrongly, i.e. with too many slices and too much bling, like 3D effects.

 

Your example of using a pie chart had me laugh out loud spontaneously. That is a brilliant use of a pie chart, especially when the meeting is scheduled for an hour. If the meeting takes more than an hour, I would even take exception to the notion that there's only one thing worse than a pie chart, and that's two pie charts.

 

But really, if the meeting is more than an hour, it would be a great visualization to show the agenda items in two pies. Plot the first 60 minutes in one pie and the remainder in another pie, which can be padded with empty minutes.

 

Then overlay the pie with the minute hand of a clock and show it as an animated chart that updates every minute. 

 

Best pie chart ever!

Sean
Community Champion
Community Champion

@Anonymous You need a Rank Measure and then add that Measure in the Visual Level Filter as in the picture...

 

then in the Visual Level Filter select => is less than and type 11 (to see the top 10)

 

Display only Top N.png

 

 

Is RANK X dynamic? for example I calculate the top 10 nationalities of a population, then create a chart displaying the top 10 nationalities of the female population in 2016, will RANK X output readjust accordingly?

 

It seems to me that won't work. The top 10 limit should be set as a setting of the visual ideally.

Anonymous
Not applicable

HI @Sean,

 

I tried to do exactly as your example, the number of values being presented is more than 10, I took one print, where is the error?


Top 10 Suppliers Purchase Order = CALCULATE (RANKX (DMEGF_Fornecedor; sum (Facts [Vl_PedidoCompra]);; 0; Dense), ALL (Facts [Vl_PedidoCompra]))

 

 

Untitled.png

 

Sean
Community Champion
Community Champion

@Anonymous  try this

 

Measure = RANKX (ALL(DMEGF_Fornecedor[Fornecedor]),  CALCULATE(SUM(Facts [Vl_PedidoCompra])))
XiB
Frequent Visitor

@Sean Hi I have tried inserting this > Measure = RANKX(ALL(vwHMSCustPerfReport[ShipperConsigneeName]),  CALCULATE(SUM(vwHMSCustPerfReport[Amount]))) There is no error, but it doesnt work. Did I use it wrongly? 

@Sean Excellent tip... This actually works!!! Thanks

Hello Sean

I wanted to display TOP N values in my PBI graph but I have a little different scenario.

1st value for graph: I am grouping columns (lets say employee_name) and using count function with it. 
2nd value for graph: District where he belongs.

I'm getting results as expected but now I want to filter TOP 10 but don't know how to do that.

Anonymous
Not applicable

@Sean

 

Thank you worked, if I want to apply an ascending or descending sort that extent as I do?

Sean
Community Champion
Community Champion

@Anonymous Select the chart and then - click the 3 dots in top right corner of the chart...

Sort Chart.png

 

Anonymous
Not applicable

can you try the example below?. and make sure you replace your measures and Dimension 

 

InternetTotalSales = SUM( InternetSales_USD[SalesAmount_USD]) // replace with your measure here
BottomRank = RANKX(ALLSELECTED('Product'[ProductName]),[InternetTotalSales],,ASC,Dense)
BottomSalesByProduct = IF([BottomRank] <= 10),[InternetTotalSales])

 

 

then add the last measure to your chart

I shall add to the @Sean solution. If we can put an if condition and give default Rank value to all ranks more than 10 to not disturb the proportions on pie chart. Since its visualisation and pie chart shows proportion, seeing only top 10 over whole pie can interpret different proportions.

@achinm45 That is another reason not to use a pie chart in this scenario.  A horizontal bar chart does not need to add up to 100 %, whereas in a pie chart, showing only part of the data distorts their absolute value, there would have to be an 11th data point summing up all "other" data.

Sean
Community Champion
Community Champion

@teylyn You really don't like pie charts Smiley Very HappySmiley LOLSmiley Very Happy

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.