Top N and others at the same time

[Juramirez]

Hi all

I'm wondering if is possible to show the the top 10 values (1,2,3,4,5...10) and in the same view see a field to values that doesn't fit  as others. I follow the steps provided in this link but it displays the top like this:

And what I want is to see the values that are in top 10 in first view with the others show as Others.

Any help is appreciated.

Regards,

J.

[TomMartens]

Hey @Juramirez,

here you will find your pbix file with my additions.

I created two unrelated tables (one for the column ID2 and ID) and the corresponding measures.

Here the measure for the table "Unrelated ID2"

Unrelated ID2 = UNION(VALUES('Table1'[ID2]),ROW("ID2","other"))  

and the Measure the corresponds to the table "Unrelated ID2"

ID2 - Top N and Other = 
var thetopn = CALCULATETABLE(TOPN([TopN Value],VALUES(Table1[ID2]),CALCULATE(SUM('Table1'[Amount]))))
var other = ROW("ID2", "Other")
var allTheRest = CALCULATE(SUM(Table1[Amount]), EXCEPT(VALUES('Table1'[ID2]),thetopn))
var theUnion = UNION(thetopn,other)
return
SUMX(
    INTERSECT('Unrelated ID2',theUnion)
    ,
    var currentIterator = 'Unrelated ID2'[ID2]
    return
    IF(
        'Unrelated ID2'[ID2] <> "Other"   
        ,CALCULATE(
            SUM('Table1'[Amount])
            ,'Table1'[ID2] = currentIterator
        )
        ,allTheRest
    )
)

I also created a Parameter table that can be used to select the N of the Top N thing, see the Measure "TopN Value" in the statement above.

This allows to create a report that looks like this:

From a personal perspective I would not recomment to use pies, instead I would use bar charts.

And I also wasn't sure what column you wanted to group "id2" or "id", I just found 9 distinct values in column ID2.

Nevertheless, I hope this is what you are looking for.

Regards,

Tom

[LeroyPaul]

Hey @TomMartens ,

Just found your answer, Thank you very much

It help me a lot !

Paul

[TomMartens]

Perfect!

[Anonymous]

@TomMartens 

what if we want to display Top N items in the Slicer OR we could display Top N + Others in the Slicer? Is it possible to achieve it?

My whole intention to achieve a slicer in descending order based on a numeric field. e.g. Products based on total sales 

How to attach a file here? I can share it and show you.

[lravikiran]

Hi ,

Can you please explain you code . It really would be very helpful

[Johanna_7]

Hi @TomMartens,

the Measure is working quite well in my file. Thanks for the help.

Now wanted to see more details, so I added a new cloumn "Citiys" from Table1.

ID is "Coutomer" in my file, I wanted to show the Amount of the Top 3 Customer and Rest with all their Citys.

On the highest Level everythig is still working perfectly, but when i drill down the Matrix the Top3 an Rest is not working anymore. The result is I get all Customers.

Thanks for your help.

Johanna

[TomMartens]

Hey Johanna,

can you please share a link to your file.

I'm not sure what you expect to see when you drill down from one of the TOPN countries to the next level, meaning cities.

• All the cities in that specific country
• The TOPN cities + other for each country
• ...

Thanks,

Tom 

[Johanna7]

Hey @TomMartens,

I expect the Top N Countries and Rest, for example in Europe, with all their cities (with amount).

I took your file from a former Post.

If it's drilled up, everything looks fine

Top n and RestTop n and Rest drilled down - not longer the Top3 and the Rest

When I drill down, I do not have the top 3 anymore.

Thanks, for your help.

Johanna

[Juramirez]

Thanks for your answer @TomMartens but I tried to do the same in my pbix and it doesn't work. The attached workbook is the same that i upload last week so I can't see the changes you're mentioning.

Regards,

J.

[TomMartens · Accepted solution]

Hey @Juramirez,

I just downloaded my linked pbix file and I see this content:

Maybe you can try again to download the workbook from the link in my previous post.

Regards,

Tom

[Juramirez]

Thank you so much @TomMartens. That's what I was looking for.

Regards!

J.

[v-juanli-msft]

Hi @Juramirez

Would you like the visual show values of top1,top2...top10 and "others" as below?

If so, create columns 

rank = RANKX(ALL(Table1),[sales],,DESC)

category = IF([rank]<=10,CONCATENATE("top",[rank]),"others")

Best Regards

Maggie

[urpalani]

Hi, Is it possible to Combine all Others as one and show the Total amount in side ?

[Juramirez]

Thanks both for your answers.

@v-juanli-msft, it looks good but if I have a permanent filter, how can I do to calculate the rank regarding this filter? For example, just for month=10. 

And besides top1, top 2, top 3.... How can I display the real value? Like Pear, Apple, Peach, etc...

[TomMartens]

Hey @Juramirez,

can you please create a pbix that contains sample data and upload the file to onedrive or dropbox and share the link.

Regards,

Tom

[Juramirez]

Hi @TomMartens

Here you can download the pbix: Link

What I want is to filter the pie chart with values where FilterField=6. When I put that filter in Filters, all values in pie chart are shown as Others. Also, when top 10 is visible, what I want to see are the values in ID, not the values as Top1, Top 2,...., Top10.

Thanks for your help!

Regards,

J.

[v-juanli-msft]

Hi @Juramirez

As tested,i can only make the pie chart show "others" when the filter applied.

To make top 10 visible and values of ID shown, I need to create an other pie chart.

Also i need to edit interactions among this visual.

Does this meet your needs?

Best Regards

Maggie

[Juramirez]

Hi  @v-juanli-msft, thanks for your answer.

Actually it's like I have my pay charts. What I want is to have both, Top 10 & Others in same pay chart for FilterField=6.

Regards,

Julián

[TomMartens]

Hey,

I'm wondering if this blog post provides what you are looking for:

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

Regards,
Tom