Dynamic Top N and Others category

[Anonymous]

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!

[Anonymous]

I am trying to get this to work for a treemap. I have double checked and I have the exact same syntax, I've put my 'y axis values' on 'groups' (because there are groups rather than an axis in a treemap), and my measures in values. It has not worked at all. I get an 'others' aggregate that sums all values including my top N values, which is entirely useless. Has anyone else gotten this solution to work for an equivalent problem but just using a treemap?

[v-yuezhe-msft]

@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

[Anonymous]

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

[Anonymous]

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!

[v-yuezhe-msft · Accepted solution]

@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

[Sid0609]

Hi @v-yuezhe-msft ,

I have used your method to create TopX and Others measures and it works fine, however as a next step if I want to see the customers that were categorised as Others, how can I do that? Your code does not allow me to see this list of Customers.

[Anonymous]

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.

[Anonymous]

Hey Lydia @v-yuezhe-msft ,

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

Regards,

Shivani

[Anonymous]

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