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
Lupiyo
Regular Visitor

Top N + Other + One specific item

Hello All!

 

I'd like to know if there is a way how to build a line chart with the Top N Filter + Other + Specifc item. I was asked to add one item that doesn't show in top N because it is too low, but we need to show it anyway.

 

Ex:

Table food

 

Potato - 500

Rice - 300

Bean - 400

Onion - 250

Carrot - 50

Meat - 800

 

Now I'll show what i want:

 

Top 3 table + other + Carrot:

Meat   - 800

Potato - 500

Bean   - 400

Other  - 550 (rice + onion)

Carrot - 50

 

So the ideia is to force this item (Carrot) to enter in top 3 even if it is high or low..

 

Thank you!!

 

 

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi,@Lupiyo

    After my research, I did a demo with reference to this article:https://community.powerbi.com/t5/Desktop/Show-Top-N-and-sum-up-the-Others-as-a-value/m-p/313063#M138...

 

Basic table

13.PNG

Result:

14.PNG

in this demo, One specific item needs to be the specific rank of all categories.

 

Here is demo , please  try it.

https://www.dropbox.com/s/aav3f2g16zb92t6/Top%20N%20and%20Others%20-%20monthly.pbix?dl=0

 

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

hi,@Lupiyo

    After my research, I did a demo with reference to this article:https://community.powerbi.com/t5/Desktop/Show-Top-N-and-sum-up-the-Others-as-a-value/m-p/313063#M138...

 

Basic table

13.PNG

Result:

14.PNG

in this demo, One specific item needs to be the specific rank of all categories.

 

Here is demo , please  try it.

https://www.dropbox.com/s/aav3f2g16zb92t6/Top%20N%20and%20Others%20-%20monthly.pbix?dl=0

 

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
TomMartens
Super User
Super User

Hey,

 

I guess you can adopt this solution to your needs:

https://www.minceddata.info/2018/06/06/topsomething-and-all-the-rest-called-other/

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

I was studing this code, i'm not familiar with the sintax but I could note a couple of things and have doubts:

 

 

top 5 and other =
var top5 = CALCULATETABLE(TOPN(5,VALUES(Table1[Month]),CALCULATE(SUM('Table1'[Amount]))))
var other = ROW("Month", "Other")
var allTheRest = CALCULATE(SUM(Table1[Amount]), EXCEPT(VALUES('Table1'[Month]),top5))
var theUnion = UNION(top5,other)
return
SUMX(
    INTERSECT('Unrelated',theUnion)
    ,
    var currentIterator = 'Unrelated'[Month]
    return
    IF(
        'Unrelated'[Month] <> "Other"   
        ,CALCULATE(
            SUM('Table1'[Amount])
            ,'Table1'[Month] = currentIterator
        )
        ,allTheRest
    )
)

When i consider all the rest, i'm excluding top5, but what about the "other"? all te rest should be the others, in my case, and the other are the carrot?

 

Hey,

 

can you please provide a PBIX file with sample data, upload the file to onedrive and dropbox, and share the link.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.