Dynamic TopN + specific company / Dynamic Top N and Others category and own company category

[Anonymous]

Hello Everyone!

I have a table 'DATA' which has the following columns: Company, Market (e.g. car, tram, bicycle), Sales, Date. 

By now, I used the function rankx to categorize the companys into top 10 and others. Then I built a dynamic Top10 Table to show the Top10 companys per market (which is filtered via dataslicer). My Problem is, that I always want to track a specific company (same company for every market) in every market. The company needs to be shown in the top10 table, no matter of her own rank (even if not <=10). So far I tried to do a workaround by splitting up the companys in TopN, Others and specific company but this didn´t work out.

Do you have any idea, on how to achieve, that the table always shows that specific company and only Top 10 (or Top 9)?

Please help!

[v-kelly-msft]

Hi @Anonymous ，

Could you pls provide some sample data？

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi Kelly,

I´ll attached you some sample data. As seen from the data, the specific company is mostly not part of the top10 (each day), but sometimes (market: Bicycle, date: 05.08.2020) it is part of the top10.

Market	Company	Sales	Date
Car	Company1	10	04.08.2020
Car	Company1	3	04.08.2020
Car	Company1	9	04.08.2020
Car	Company2	7	04.08.2020
Car	Company2	2	04.08.2020
Car	Company3	15	04.08.2020
Car	Company4	11	04.08.2020
Car	Company5	19	04.08.2020
Car	specificCompany	4	04.08.2020
Car	Company7	35	04.08.2020
Car	Company8	24	04.08.2020
Car	Company9	37	04.08.2020
Car	Company10	12	04.08.2020
Car	Company11	49	04.08.2020
Car	Company12	28	04.08.2020
Car	Company1	9	05.08.2020
Car	Company1	2	05.08.2020
Car	Company1	8	05.08.2020
Car	Company2	6	05.08.2020
Car	Company2	1	05.08.2020
Car	Company3	14	05.08.2020
Car	Company4	10	05.08.2020
Car	Company5	18	05.08.2020
Car	specificCompany	3	05.08.2020
Car	Company7	34	05.08.2020
Car	Company8	23	05.08.2020
Car	Company9	36	05.08.2020
Car	Company10	11	05.08.2020
Car	Company11	48	05.08.2020
Car	Company12	27	05.08.2020
Bicycle	Company1	12	04.08.2020
Bicycle	Company1	5	04.08.2020
Bicycle	Company1	11	04.08.2020
Bicycle	Company2	9	04.08.2020
Bicycle	Company2	4	04.08.2020
Bicycle	Company3	17	04.08.2020
Bicycle	Company4	13	04.08.2020
Bicycle	Company5	21	04.08.2020
Bicycle	specificCompany	6	04.08.2020
Bicycle	Company7	37	04.08.2020
Bicycle	Company8	26	04.08.2020
Bicycle	Company9	39	04.08.2020
Bicycle	Company10	14	04.08.2020
Bicycle	Company11	51	04.08.2020
Bicycle	Company12	30	04.08.2020
Bicycle	Company1	17	05.08.2020
Bicycle	Company1	10	05.08.2020
Bicycle	Company1	16	05.08.2020
Bicycle	Company2	14	05.08.2020
Bicycle	Company2	9	05.08.2020
Bicycle	Company3	22	05.08.2020
Bicycle	Company4	18	05.08.2020
Bicycle	Company5	26	05.08.2020
Bicycle	specificCompany	49	05.08.2020
Bicycle	Company7	42	05.08.2020
Bicycle	Company8	31	05.08.2020
Bicycle	Company9	44	05.08.2020
Bicycle	Company10	19	05.08.2020
Bicycle	Company11	56	05.08.2020
Bicycle	Company12	35	05.08.2020

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

Sorry for the late reply.

Create a measure as below:

Measure = 
var _topn=TOPN(10,ALLSELECTED('Table'),'Table'[Sales],DESC)
Return
CALCULATE(MAX('Table'[Sales]),KEEPFILTERS(_topn))

And you will see:

The top 10 will be based on your slicers.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , refer if this can help

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

Or top N like

Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))

[Anonymous]

Thanks for your feedback! The shown instructions are well written on how to rank TopN (Top10). But I need to know on how to show a table including only topN + one more specific company. 

Do you have further ideas on how show top9 + a specific company?

Best Regards

Jonas