Forum Discussion

SDittmannFleet's avatar
SDittmannFleet
Icon for Advocate IV rankAdvocate IV
2 years ago

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 m...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

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

  • SDittmannFleet's avatar
    SDittmannFleet
    Icon for Advocate IV rankAdvocate IV
    2 years ago

    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])
        )
    )
    • SDittmannFleet's avatar
      SDittmannFleet
      Icon for Advocate IV rankAdvocate IV
      2 years ago

      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.

      • SDittmannFleet's avatar
        SDittmannFleet
        Icon for Advocate IV rankAdvocate IV
        2 years ago

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