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.
4 REPLIES 4
Highlighted
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.
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