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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Amos_Lim
Resolver I
Resolver I

Top 5 won't work when filter catergory selected

I have a chart that shows the top five.

When no filter is selected, it works flawlessly.

When a filter was applied, however, instead of showing the top 5, it displayed the top 6.

Please see below the 2 photos with and without filter selected.

 

How can I ensure that it never exceeds 5?

Need help! please. Thanks.

Without Filter.JPGWith Filter.JPG

Filter.JPG

2 ACCEPTED SOLUTIONS

Hi,

There is a tie at the 5th place - that's the reason you see 6 rows.  PBI file attached.

Ashish_Mathur_0-1701567619726.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Amos_Lim
Resolver I
Resolver I

Thank you so much, Ashish Mathur.

I tested it on more data sets and it seemed to work.

I'll run another test on my master file.

 

View solution in original post

9 REPLIES 9
Amos_Lim
Resolver I
Resolver I

Thank you so much, Ashish Mathur.

I tested it on more data sets and it seemed to work.

I'll run another test on my master file.

 

You are welcome.  Please mark my reply as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

Try these measure patterns

Count = counta(Data[Subsystem])

Top 5 = calculate([Count],Topn(5,all(Data[Area]),[Count]),values(Data[Area]))

Area is what i see in the legend of the pie chart.  Drag he Top 5 measure to the visual.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello, Ashish_Mathur.

I was able to get the first measure, Count = counta(Data[Subsystem]), to work.

In the second part, you mention noticing the "Area" in the pie chart's legend? There wasn't any Area in my chart. could you clarify?

I have assumed that AC,O,FS,DS are in the Area column of the Table.  chenge that accordingly to your data.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Yes, that was the subsystem, and I replaced both the count and the Top 5 with the following:-

Count = COUNTA('Data Master'[Subsystem])

Top 5 = CALCULATE([Count],TOPN(5,ALL('Data Master'[Subsystem]),[Count]),VALUES('Data Master'[Subsystem]))

 

I replaced the filters as shown below, but I still received the same result. Am I doing something wrong with the filters?

 

Filter.JPGSameResult.JPG

Clear the filter from the filter pane.  If it still does not help, then share the download link of the PBI file.  Show the problem there very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish Mathur,

Please see the sample file as attached.

Test Sample File 

Hi,

There is a tie at the 5th place - that's the reason you see 6 rows.  PBI file attached.

Ashish_Mathur_0-1701567619726.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.