Help with a top 10 and others rows in the table

[Anonymous]

Hello all!!

I hope someone can help with this:

I want to create a top 10 for the vendor column and the rest of the vendor clasified like "others"

Im trying with this formula in a column:

Top10 by Amount =

var rankvendor = RANKX(ALL(Sheet2[Vendor]),[Total YTD],,DESC)

return

IF (rankvendor<=9,Sheet2[Vendor],"Others")

This should work dynamically for all the contries and areas.

I attach the example in pbi,

https://drive.google.com/file/d/1JIGKnVTrhJfaaszEguUnitQZ4EyXOtnx/view?usp=sharing 

Thanks for the help.

Monica

[v-xicai]

Hi @Anonymous ,

You couldn't have a calculated column interact with a slicer or other visuals,  since columns are only calculated at data load/refresh. 

So you may create the [Top10 by Amount] using a measure instead of calculated column.

Top10 by Amount =

var rankvendor = RANKX(ALLSELECTED(Sheet2),[Total YTD],,DESC)

return

IF (rankvendor<=9, MAX(Sheet2[Vendor] ),"Others")

Best Regards,

Amy 

Community Support Team _ Amy

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

[Anonymous]

Thanks for your help @v-xicai 

I dont use a measure, because  I want a result like this:

Vendor 1          9999

Vendor 2          8888

Vendor 3         7777

........

Others            the sum of the rest of vendor that dont exist in the rank from 1-9

For this reason I try to do it in a column, but I can't find a way to make it work.

Thanks

Monica

[v-xicai]

Hi @Anonymous ,

You may create calculated column  [Top10 by Amount] using DAX below, which just changes the ALL function to ALLSELECTED function to take effect of slicers.  While you still couldn't have a calculated column interact with a slicer or other visuals immediately like measure,  since columns are only calculated at data load/refresh. So you may need to click "Refresh" button to get updated once you make changes in slicers.

Top10  by Amount =

var rankvendor = RANKX(ALLSELECTED(Sheet2),[Total YTD],,DESC)

return

IF (rankvendor<=9, Sheet2[Vendor] ,"Others")

Then you may display it in Table visual, and also put [Total YTD] into table visual later.

Best Regards,

Amy 

Community Support Team _ Amy

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

[harshnathani]

Hi @Anonymous ,

Below is link of a video which guides you step by step to create TOPN and others.

https://www.youtube.com/watch?v=UAnylK9bm1I

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@Anonymous , refer if this can help

https://www.proserveit.com/blog/ms-power-bi-topn-and-other

https://community.powerbi.com/t5/Desktop/Top-10-Other/td-p/51282

Also, refer :

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization