cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shivani1 Frequent Visitor
Frequent Visitor

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
Moderator v-yuezhe-msft
Moderator

Re: Dynamic Top N and Others category

@shivani1 ,

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

4 REPLIES 4
Moderator v-yuezhe-msft
Moderator

Re: Dynamic Top N and Others category

@shivani1,

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.
shivani1 Frequent Visitor
Frequent Visitor

Re: Dynamic Top N and Others category

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

Moderator v-yuezhe-msft
Moderator

Re: Dynamic Top N and Others category

@shivani1 ,

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

shivani1 Frequent Visitor
Frequent Visitor

Re: Dynamic Top N and Others category

Hey Lydia @v-yuezhe-msft ,

 

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

 

Regards,

Shivani

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 80 members 1,323 guests
Please welcome our newest community members: