Forum Discussion

KN8's avatar
KN8
Frequent Visitor
4 years ago
Solved

Top N filter + other

Hello Everyone,

 

I'm trying to achive TOP 3 + Other(Oracle) Companies by revenue  from the below table. I was able to get Top 3 by applying TOP N filter on the visual. 

How can we achieve  TOP N + "Other" (in this case other = "Oracle") ? 

 

Input Data :

DateCompanies Revenue
7/10/2021Google1000
8/15/2021Google1500
9/5/2021Apple1000
7/10/2021Facebook1000
8/11/2021Apple500
8/18/2021Microsoft800
9/14/2021IBM600
9/4/2021Oracle400
8/20/2021Tesla2000

 

Expected Output:

TOP 3 "Companies + Oracle" by Revenue

Companies Revenue
Google2500
Tesla2000
Apple1500
Oracle400

 

Thanks in advance for your help!!

Need Help
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Only show top3 and Oracle: =
    VAR top3table =
    TOPN ( 3, ALL ( Data[Companies] ), CALCULATE ( SUM ( Data[Revenue] ) ), DESC )
    RETURN
    IF (
    HASONEVALUE ( Data[Companies] ),
    SWITCH (
    TRUE (),
    SELECTEDVALUE ( Data[Companies] ) = "Oracle", SUM ( Data[Revenue] ),
    CALCULATE ( SUM ( Data[Revenue] ), KEEPFILTERS ( top3table ) )
    )
    )

     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi, 

    Thank you for your message.

    When you see the previous measure, there is a IF condition that says "if there is one company in the row, then show the result, otherwise blank".

    if I do not write this, then it shows the wrong total, and I did not know whether readers want to see the total or not. I thought showing blank is better than showing a wrong total.

    If you want to show the total, then please try the below.

     

    Only show top3 and Oracle: =
    VAR top3table =
    TOPN ( 3, ALL ( Data[Companies] ), CALCULATE ( SUM ( Data[Revenue] ) ), DESC )
    RETURN
    SUMX (
    VALUES ( Data[Companies] ),
    CALCULATE (
    SWITCH (
    TRUE (),
    SELECTEDVALUE ( Data[Companies] ) = "Oracle", SUM ( Data[Revenue] ),
    CALCULATE ( SUM ( Data[Revenue] ), KEEPFILTERS ( top3table ) )
    )
    )
    )
     

7 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    Hi KN8 

     

    You can use SUMMARIZE to create a New Table using DAX:

    New Table = SUMMARIZE ( 'Table' , Table[Companies] )

    From there, you can use the below to rank the companies:

     

    Top 3 Rank Companies = 

     VAR _RankByRev = RANKX ( ALL ( 'New Table' ) , [Sum Revenue] , , DESC )

     return

     IF ( _RankByRev <=3 , 'New Table'[Companies] , "Other" )

    This will return the top 3 names and all others as Other.  I assume you have a [Sum Revenue] which is just a SUM on the revenue column in your Table.  Outputs will be as per below:

     

    From here, you can use the [Top 3 Rank Companies] column as the basis of filtering and just drag the [Sum Revenue] measure as you require.

     

    Hope this helps 🙂

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Only show top3 and Oracle: =
    VAR top3table =
    TOPN ( 3, ALL ( Data[Companies] ), CALCULATE ( SUM ( Data[Revenue] ) ), DESC )
    RETURN
    IF (
    HASONEVALUE ( Data[Companies] ),
    SWITCH (
    TRUE (),
    SELECTEDVALUE ( Data[Companies] ) = "Oracle", SUM ( Data[Revenue] ),
    CALCULATE ( SUM ( Data[Revenue] ), KEEPFILTERS ( top3table ) )
    )
    )

     

     

    • KN8's avatar
      KN8
      Frequent Visitor
      4 years ago

      Thanks for the solution. May I know why it doesn't aggregate to grand total ?

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi, 

        Thank you for your message.

        When you see the previous measure, there is a IF condition that says "if there is one company in the row, then show the result, otherwise blank".

        if I do not write this, then it shows the wrong total, and I did not know whether readers want to see the total or not. I thought showing blank is better than showing a wrong total.

        If you want to show the total, then please try the below.

         

        Only show top3 and Oracle: =
        VAR top3table =
        TOPN ( 3, ALL ( Data[Companies] ), CALCULATE ( SUM ( Data[Revenue] ) ), DESC )
        RETURN
        SUMX (
        VALUES ( Data[Companies] ),
        CALCULATE (
        SWITCH (
        TRUE (),
        SELECTEDVALUE ( Data[Companies] ) = "Oracle", SUM ( Data[Revenue] ),
        CALCULATE ( SUM ( Data[Revenue] ), KEEPFILTERS ( top3table ) )
        )
        )
        )
         
  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    Hi @KN8 

     

    You can use SUMMARIZE to create a New Table using DAX:

    New Table = SUMMARIZE ( 'Table' , Table[Companies] )

    From there, you can use the below to rank the companies:

     

    Top 3 Rank Companies = 

     VAR _RankByRev = RANKX ( ALL ( 'New Table' ) , [Sum Revenue] , , DESC )

     return

     IF ( _RankByRev <=3 , 'New Table'[Companies] , "Other" )

    This will return the top 3 names and all others as Other.  I assume you have a [Sum Revenue] which is just a SUM on the revenue column in your Table.  Outputs will be as per below:

     

    From here, you can use the [Top 3 Rank Companies] column as the basis of filtering and just drag the [Sum Revenue] measure as you require.

     

    Hope this helps ğŸ™‚