cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Dynamic Top N and Others category

Hello Everyone!

 

I have a table 'Data' which has the following columns: Customer, Income, Volume, Region and Date.

I want to create a report such that Region and Date are used as slicers and a combination chart to show Customer, Income and Volume.

The issue I am facing here is, I want to view my top 10 customers in the chart and categorize my remaining customers as Others.

This should be dynamic in such a way that if I select Asia as region for year 2017 via the slicers, the combination chart shows me my top 10 customers along with the others category for the particular selection.

 

Is there a way we can achieve this? Please help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@Anonymous ,

Create a new table using dax below. And create TopnN Filter table using enter data in Power BI Desktop.

CustomerName = 
    UNION ( VALUES ( Table1[Customer Name] ), ROW ( "CustomerName", "Others" ) )


Create the following column in your original table.

all = Table1[Profit]*Table1[Volume]


Create the following measures. For more details, please check attached PBIX file.

Sum = SUM(Table1[all])
Top X = 
   
	VAR TopNumber =if(HASONEVALUE('TopN Filter'[TopN]),VALUES('TopN Filter'[TopN]),10)
    VAR Rest =
        COUNTROWS ( Table1 ) - TopNumber
    RETURN
        IF (
            HASONEVALUE ( CustomerName[Customer Name] ),
            CALCULATE (
                [Sum],
                FILTER (
                    Table1,
                    [Customer Name] = VALUES ( CustomerName[Customer Name])
                        && CONTAINS (
                            TOPN (
                                TopNumber,
                                ADDCOLUMNS (
                                    ALL ( CustomerName[Customer Name] ),
                                    "Income", CALCULATE (
                                        [Sum],
                                        FILTER ( Table1, Table1[Customer Name] = EARLIER ( [Customer Name]) )
                                    )
                                ),
                                [Income], DESC
                            ),
                            CustomerName[Customer Name], VALUES (CustomerName[Customer Name] )
                        )
                )
            )
        )
Other = 
    
	VAR TopNumber = if(HASONEVALUE('TopN Filter'[TopN]),VALUES('TopN Filter'[TopN]),10)
    VAR Rest =
        COUNTROWS ( Table1 ) - TopNumber
    RETURN
        IF (
            HASONEVALUE ( CustomerName[Customer Name] ),
            IF (
                VALUES (  CustomerName[Customer Name]) = "Others",
                SUMX (
                    TOPN (
                        Rest,
                        ADDCOLUMNS (
                            VALUES ( Table1[Customer Name] ),
                            "Measure", [Sum]
                        ),
                        [Measure], ASC
                    ),
                    [Measure]
                )
            )
        )



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Highlighted
Microsoft
Microsoft

@Anonymous,

Please take a look at the method in the following blog. If you have any questions about DAX, please share sample data of your table here.

https://www.oraylis.de/blog/show-top-n-and-rest-in-power-bi



Reagrds,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Anonymous
Not applicable

Hi @v-yuezhe-msft,

Thank you so much for the blog post but I am fairly new to Power BI and I'm unable to achieve this. Could you please help me out with the DAX here? Also, I will be using District and Date as slicers. Below is the link to the sample data!

Sample Data

 

Thanks,

Shivani

Highlighted

@Anonymous ,

Create a new table using dax below. And create TopnN Filter table using enter data in Power BI Desktop.

CustomerName = 
    UNION ( VALUES ( Table1[Customer Name] ), ROW ( "CustomerName", "Others" ) )


Create the following column in your original table.

all = Table1[Profit]*Table1[Volume]


Create the following measures. For more details, please check attached PBIX file.

Sum = SUM(Table1[all])
Top X = 
   
	VAR TopNumber =if(HASONEVALUE('TopN Filter'[TopN]),VALUES('TopN Filter'[TopN]),10)
    VAR Rest =
        COUNTROWS ( Table1 ) - TopNumber
    RETURN
        IF (
            HASONEVALUE ( CustomerName[Customer Name] ),
            CALCULATE (
                [Sum],
                FILTER (
                    Table1,
                    [Customer Name] = VALUES ( CustomerName[Customer Name])
                        && CONTAINS (
                            TOPN (
                                TopNumber,
                                ADDCOLUMNS (
                                    ALL ( CustomerName[Customer Name] ),
                                    "Income", CALCULATE (
                                        [Sum],
                                        FILTER ( Table1, Table1[Customer Name] = EARLIER ( [Customer Name]) )
                                    )
                                ),
                                [Income], DESC
                            ),
                            CustomerName[Customer Name], VALUES (CustomerName[Customer Name] )
                        )
                )
            )
        )
Other = 
    
	VAR TopNumber = if(HASONEVALUE('TopN Filter'[TopN]),VALUES('TopN Filter'[TopN]),10)
    VAR Rest =
        COUNTROWS ( Table1 ) - TopNumber
    RETURN
        IF (
            HASONEVALUE ( CustomerName[Customer Name] ),
            IF (
                VALUES (  CustomerName[Customer Name]) = "Others",
                SUMX (
                    TOPN (
                        Rest,
                        ADDCOLUMNS (
                            VALUES ( Table1[Customer Name] ),
                            "Measure", [Sum]
                        ),
                        [Measure], ASC
                    ),
                    [Measure]
                )
            )
        )



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Anonymous
Not applicable

Hey Lydia @v-yuezhe-msft ,

 

Thanks a ton for all the help! Works just the way I wanted it 🙂

 

Regards,

Shivani

Highlighted

Hi Shivani,

 

there is also another blog post, showing how to rank and categorize your data:

https://livingandcoding.com/blog/top-n-others-power-bi/

Maybe this helps.

 

Cheers!

Highlighted

Hi Shivani,

 

I have same kind of Issue..

Supplier        value       

a                    100

b                      99

c                      16

h                      76 

In this case i just want create the separate ''card visual'' to high light the highest supplier and value details.and this details changes on daily basis.. i used slicer table to select the date....need measure for, on the slicer table date and the highest value of the supplier name and values.

Kindly suggest.

 

Thanks

Kavitha

Highlighted

Hi, i used this and works fine.
My problem now is that previously i have an a page of tool reports type that shows information about the client when i pass the mouse cursor over the the name of the customer in the chart, but now this not work obviously because the name in the chart cames from the new table that we created, and it´s not possible to create a relatioship between the new table with only the customers name plus others because a circular reference woul be created.  any solution type?

 

Thanks in advance.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors