Forum Discussion

bergen288's avatar
bergen288
Icon for Helper IV rankHelper IV
3 years ago

Top N filter with multiple categorical columns

I have a table visual with multiple categorical columns (Sponsor ID, Online Name, Subscriber ID, Last Name, Remit Center, Status) and descending sorted by Payment Amount as shown below.  I would like...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

bergen288 , Try like

2 column top N = CALCULATE([Net], TOPN(10, SUMMARIZE(ALLSELECTED('Item'), 'Item'[Brand], 'Item'[Category]), [Net],DESC),VALUES('Item'[Brand]), VALUES('Item'[Category]) )

 

or

 

2 column top N = CALCULATE([Net], keepfilters( TOPN(10, SUMMARIZE(ALLSELECTED('Item'), 'Item'[Brand], 'Item'[Category]), [Net],DESC) ) )

bergen288's avatar
bergen288
Icon for Helper IV rankHelper IV
3 years ago

amitchandak:

I tried a simple table with 2 categorical columns first.  Below is my measure based on your 2nd suggestion: 

Top 10 by Tax ID and Name =
    VAR __AMT = SUM('PBI_XZ_Trial_Deposits_Final'[Amt])
    VAR __SUM = SUMMARIZE(ALLSELECTED('PBI_XZ_Trial_Deposits_Final'), 'PBI_XZ_Trial_Deposits_Final'[SBSR_TAX_ID], 'PBI_XZ_Trial_Deposits_Final'[SBSR_FULL_NAME])
    RETURN
    CALCULATE(__Amt, keepfilters( TOPN(10, __SUM, __Amt, DESC) ) )
 
However, it gives me the same result of 'PBI_XZ_Trial_Deposits_Final'[Amt].  The same is true for your 1st suggestion.  What's wrong with it?
Thanks.