Top N filter + other

[KN8]

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 :

Date	Companies	Revenue
7/10/2021	Google	1000
8/15/2021	Google	1500
9/5/2021	Apple	1000
7/10/2021	Facebook	1000
8/11/2021	Apple	500
8/18/2021	Microsoft	800
9/14/2021	IBM	600
9/4/2021	Oracle	400
8/20/2021	Tesla	2000

Expected Output:

TOP 3 "Companies + Oracle" by Revenue

Companies	Revenue
Google	2500
Tesla	2000
Apple	1500
Oracle	400

Thanks in advance for your help!!

[Jihwan_Kim · Accepted solution]

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 ) )
)
)

[TheoC]

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 · Accepted solution]

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]

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

[Jihwan_Kim · Accepted solution]

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 ) )

)

)

)

[KN8]

Awesome thanks again for the explination and the solution. 

[TheoC]

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 🙂

[TomMartens]

Hey @KN8 ,

this article provides a solution on how to tackle your challenge: https://www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/

Hopefully, this is what you are looking for.

Regards,

Tom