Dynamic Top 10 to use as legend

[Anonymous]

Hi, hoping someone can help with a solution to my query:

In a pie chart, I'd like to be able to show the top 10 categories and an "other" category to encompass the rest. I.e. the pie chart below would only show the largest 10 segments separately and then group everything else into an "other" segment.

We need the top 10 to be dynamic so will change with filtering - we could have 2 completely different "top 10s" depending on the filters put on the chart by the end user.

Is this possible? Thank you in advance for your help!

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

In between have look at this video, this can help :https://www.youtube.com/watch?v=UAnylK9bm1I

[Anonymous]

Hi @amitchandak , Sorry, posting again as I don't think my original reply was correct:

The data tables in their most basic form look like this with a 1:1 relationship on the Key column:

Transactions:

Key	TransactionID
1	transaction001
2	transaction002
3	transaction003
4	transaction004
5	transaction005
6	transaction006
7	transaction007
8	transaction008
9	transaction009
10	transaction010

Demographics:

Key	AgeBand
1	Under 2 Yrs
2	40-45 Yrs
3	50-55 Yrs
4	50-55 Yrs
5	50-55 Yrs
6	25-30 Yrs
7	40-45 Yrs
8	30-35 Yrs
9	Over 100 Yrs
10	30-35 Yrs

The particular data above would result in the following table/piechart:

AgeBand	Count of TransactionID
Under 2 Yrs	1
25-30 Yrs	1
30-35 Yrs	2
40-45 Yrs	2
50-55 Yrs	3
Over 100 Yrs	1

but if there were more than 10 categories then the smallest categories after the top 10 would be shown as "Other".

Sorry, I didn't spot your youtube link at first - I have had a look at creating a column like the video but it doesn't seem to work 100% for me - I think it is to do with filters put on the report:

New column I tried to create in the table AgeBands, where "AgeBands" is just a table showing a list of the possible age bands (with a relationship with the AgeBand column in Demographics table) and TransactionsCount is a measure doing COUNTA(Transactions[TransactionID]):

Age Band Top 10 =

var RankAgeBandbyCount = RANKX(all(AgeBands), [TransactionsCount],,desc)

return

if(RankAgeBandbyCount<=10,AgeBands[AgeBand],"Other")

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Let me know whether you'd like to get below one:

Measure = DISTINCTCOUNT(Transactions[TransactionID])

Measure 2 = var a = RANKX(ALL(Demographics[AgeBand]),[Measure],,DESC)
return IF(a<=10,a,"Other")

If you'd like to get the calculated column, try below formula:

Column = RANKX(ALL(Demographics[AgeBand]),CALCULATE(DISTINCTCOUNT(Transactions[TransactionID]),ALLEXCEPT(Demographics,Demographics[AgeBand])),,DESC)

and set the column as "Do not summarize"