Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

TopN filtered

Hello,

 

I have the following computed table :

 

TT10 = TOPN(10, SUMMARIZE(SalesItemLedgerEntries,SalesItemLedgerEntries[CustomerNo],"TotalSalesYTD]",[TotalSalesYTD]))

I added a relation with Source table and is working fine.

BUt when I filter source table I expect that TopN to calculate Top10 from filtered table. I get Top10 calculated from entire table and is filtering only by customer .

There is a way to get Top10 from filtered table?

 

 

Need Help

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi Anonymous,

     

    I'm a little confused about your scenario. What is the filtered table?

     

    If it is convenient, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

     

    Best  Regards,
    Cherry

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Let's make a simple case :

    Given table :

    CustomerSalesPersonDateAmount
    AS11/2/2017100
    AS11/2/2018400
    Bs11/2/201750
    Bs11/2/2017200
    Bs11/2/2018200
    CS21/2/2017100
    CS21/2/2018300
    DS21/2/2018200

     

    Request :

    Create a Top N Customer, N variable from a slicer . Should include group "Others". Should be able to filter on other columns in source table.

     

    What I did

    1. I created measures

    TotalSales = SUMX(Table1,Table1[Amount])

    TotalSalesYTD = TOTALYTD([TotalSales],Table1[Date])

     

    2. Create a computed table

    TableC = TOPN(2,ALLSELECTED(Table1[Customer]),Table1[TotalSalesYTD])

    3. Create a relation between computed table and source table.

     

    Now Top 2 is working fine. result is :

    Customer TotalsalesYTD

    A                 400

    C                 300

    (blank)        600

     

    First problem is when I set with a slicer filter on salesperson S2. Computed table will show from overall Top 2 only customer with salesperson S2

     

    Customer TotalsalesYTD

    C                 300

     

    Expected is to have Top 2 for salesperson S2

    Customer TotalsalesYTD

    C                 300

    D                 200

     

    Second problem , adding a parameter and change computed table like :

    TableC = var topx = SELECTEDVALUE(TopSelect[Parameter]) return TOPN(topx,ALLSELECTED(Table1[Customer]),Table1[TotalSalesYTD])

    Is not working.

     

    Any other solution is welcome.