+1 vote

Hi, i want to categorize the data into top n data and others. how to do it? can anyone help me.

```Year    Location    Type    Amount
2015    West        Apple   12
2015    West        Pear    14
2015    East        Apple   55
2015    South       Orange  62
2015    West        Orange  64
2015    East        Banana  12
2015    North       Banana  23
2015    East        Peach   43
2015    East        Apple   89
2015    West        Banana  77
2015    West        Orange  43
2015    North       Apple   2```
`Here i want to categorize data into top n depending on amount for each type.`
Mar 18, 2019 in Power BI 5,724 views

## 1 answer to this question.

+1 vote

Hi ,

• Create a measure to calculate rank using below code.

Rank = RANKX(ALLSELECTED(Sheet1[Type]),CALCULATE(SUM(Sheet1[Amount])),,DESC,Dense)

( Here the selecting all data for type in sheet1 and then calculating sum and displaying the rank in descending order. )

• Then create another measure to group the top n as "Top N" others into "Others" group.

group = IF([Rank]<=Parameter[Parameter Value],"Top N","OTHERS")

• Add a parameter for selecting n value.​

• Then click ok. Now drag the pointer on the slicer for selecting n value.
• Then add the column for displaying top n and others as a column.

Hope this helps you.

answered Mar 18, 2019 by
• 33,030 points
Cherukuri

Your solution looks quite good for me, however; is it possible to show the Type for Top N and Others for others
for example :

Aple 158

Orange 169

Others 169

Thank you

See this blog below. It has good explanation on how to show top N and others group.

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

Thank you, I did try following the link you sent to me .
It looked 90% fine for me , except that I'd like to show the table

Aple 158
Orange 169
Others 169

=========

Total  496

Now it shows the total of 327 (Because of the filter of Rank >0). Do you have any idea if it can really show the Net Total of TopN+Others.  I was a tableau user,  it is simply just building "Set" of Top N . While I am looking for this feature in power bi and does not find one.

Thank you

0 votes
0 answers

## After connecting SharePoint List with Power BI, and editing data in query, all the data I want are in links

I've try so many ways to decode ...READ MORE

0 votes
1 answer

## Why users can't see the export option in Power Bi services? It is disabled, all users have given member permissions. However few can see and few can't? Any idea

In tenants settings under admin settings that ...READ MORE

+1 vote
1 answer

## Unable to install connector for Power Bi and PostgreSQL

I think the problem is not at ...READ MORE

+1 vote
1 answer

## Connect power bi desktop to dataset and create custom reports

Yes using Power BI REST API to ...READ MORE

+3 votes
2 answers

## Combine tables in Power BI

You can also achieve this using a ...READ MORE

0 votes
1 answer

## Power Bi Dax Table

You need to wrap the numbers in ...READ MORE

0 votes
2 answers

## Power BI Dax Multiple IF AND Statements

HI I have Column Patient 12 13 14 15 18 IN Patient=12, 13 Out ...READ MORE

0 votes
1 answer

## Distinct count filtered by condition using Power BI Dax

Try this, it should work: DistinctCountActiveMonths = CALCULATE( ...READ MORE

0 votes
1 answer

## How to categorize state, pin and city in power bi (Address)

After loading dataset, you can split in ...READ MORE

0 votes
1 answer

## Difference between measure and calculate column in power BI

Hi Rahul,  With calculated columns, you can add new ...READ MORE