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

Data Days is here! Join us now for 60+ days of learning, challenges, and connection. Learn more

Reply
SDittmannFleet
Advocate IV
Advocate IV

TOP N/BOTTOM N Filters not working as expected

I have a simple table visual. The two tables it is pulling from are connected via a one-to-many relationship. When I filter by Supplier (from table A) TOP 10 by Total Invoiced (from table B), I get many more than 10 records. I get the same results if I use fields from just table B. The same is happening when I go by BOTTOM 10, only that I get way fewer results. 

 

Table w/o filter: 

SDittmannFleet_0-1699389560800.png

Table w/ top 10 filter:

SDittmannFleet_1-1699389684012.png

You can clearly see the scroll bar.

Table w/ bottom 10 filter:

SDittmannFleet_2-1699389772540.png

When I simply sort the table, items show up that are not included in the BOTTOM 10.

 

What is happening here, and can it be corrected?

4 REPLIES 4
amitchandak
Super User
Super User

@SDittmannFleet , Can you Try TOPN Measure based on the need

 

TOP10 Brand = CALCULATE([Net], TOPN(10,ALLSELECTED('Item'[Brand]), [Net],DESC), values('Item'[Brand]))
TOP10 Category = CALCULATE([Net], TOPN(10,ALLSELECTED('Item'[Category]), [Net],DESC), values('Item'[Category]))

 

2 column top N = Sumx(Keepfilters( TOPN(10, SUMMARIZE(ALLSELECTED('Item'), 'Item'[Product Category], 'Item'[Product Name]), [Total_sales],DESC)), [Total_sales])

 

2 column top N = Sumx(Keepfilters( TOPN(10, SUMMARIZE(ALLSELECTED('Table'), 'Table'[region], 'Table'[Customer Name]), calculate(Sum(Table[Total sue])) ,DESC)),calculate(Sum(Table[Total sue])))

 

 

RANKX, TOPN, Dynamic TOPN with Numeric Parameters: https://youtu.be/cN8AO3_vmlY?t=25610

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thank you for responding. When I try creating a TOP N table, I get a completely different list with different totals. Here's the DAX code: 

Top 10 Suppliers = 
VAR Top10Suppliers =
    TOPN(10, FILTER(ALL('Account Map Supplier'), NOT(ISBLANK([Total Invoiced])) && [Total Invoiced] <> 0 && NOT(ISBLANK([Account Id]))), [Total Invoiced], DESC)

RETURN
    ADDCOLUMNS(
        Top10Suppliers,
        "Total Invoiced",
        CALCULATE ( 
            SUM ( 
                Invoice[Grand_Total__c] 
            ), 
            Invoice[Invoice Last 3 Years] = 1,
            'Account Map Supplier'[Display_Name__c] = EARLIER('Account Map Supplier'[Display_Name__c])
        )
    )

OK, I guess I am a step closer as to what is happening, at least with the Top 10: there is duplication in the supplier column, so in the end, it gives me 10 suppliers, which is what I am asking it to do, but since I can't cumulatively apply several Top N filters, I'm kind of stuck.

What the duplication doesn't explain is that I only get a handful results for the bottom 10. That one makes no sense.

Helpful resources

Announcements
Fabric Data Days is here Carousel

Fabric Data Days 2026

Don't miss out on Data Days, June 15 through August 7. Learn Fabric, Power BI, SQL, AI and more.

May Power BI Update Carousel

Power BI Monthly Update - May 2026

Check out the May 2026 Power BI update to learn about new features.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.