Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
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

@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

9 REPLIES 9
Swooze
Frequent Visitor

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
Employee
Employee

@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.
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

Anonymous
Not applicable

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!

@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.

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
Not applicable

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
Not applicable

Hey Lydia @v-yuezhe-msft ,

 

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

 

Regards,

Shivani

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors