Power BI: TopN and All Other

0 votes

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 in Power BI by Nithin
52 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

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 by CHERUKURI
• 12,780 points

Related Questions In Power BI

0 votes
1 answer

Unable to install connector for Power Bi and PostgreSQL

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

answered Aug 21, 2018 in Power BI by nirvana
• 3,040 points
337 views
0 votes
1 answer

Connect power bi desktop to dataset and create custom reports

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

answered Sep 18, 2018 in Power BI by Kalgi
• 35,720 points
47 views
0 votes
1 answer

Is government, academic and nonprofit pricing available for Power BI?

Yes, nonprofit pricing is available when purchasing ...READ MORE

answered Sep 26, 2018 in Power BI by Kalgi
• 35,720 points
25 views
0 votes
1 answer

​Embed Power BI dashboard tile and make it auto update

This feature is not currently available with ...READ MORE

answered Sep 28, 2018 in Power BI by Kalgi
• 35,720 points
83 views
+3 votes
2 answers

Combine tables in Power BI

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

answered Oct 5, 2018 in Power BI by lina
• 8,100 points

edited Oct 11, 2018 by Kalgi 83 views
0 votes
1 answer

Power Bi Dax Table

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

answered Oct 5, 2018 in Power BI by Kalgi
• 35,720 points
41 views
0 votes
1 answer

Power BI Dax Multiple IF AND Statements

I suspect your issue is that alphabetically ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 35,720 points
3,521 views
0 votes
1 answer

Distinct count filtered by condition using Power BI Dax

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

answered Oct 5, 2018 in Power BI by Kalgi
• 35,720 points
1,885 views
0 votes
1 answer

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

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

answered May 9 in Power BI by anonymous
• 12,780 points
7 views
0 votes
1 answer

Difference between measure and calculate column in power BI

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

answered 3 days ago in Power BI by CHERUKURI
• 12,780 points
9 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.