Power BI TOPN + Others
Problem Statement: We would like to display the top 5 or the Top 10 with others. Other than the top 5/10 items, the rest are grouped into others.
Solution: This solution requires us to use the extended dimension approach we have used in some of our previous blogs, such as showing the overall total on the bar visual and showing the unselected values as others.
It is the standard sales model that I use for all my videos and blogs, and I am also using it for my YouTube channel. A sales fact that is joined with dimensions such as Item, Geography, Date, and Customer in addition to the key measure [net].
The measure [NET] in the schema
net = [Gross] -[Discount]
Gross = Sum(Sales[Gross Sales Amount])
Discount= Sum(Sales[Discount Amount])In order to display top brands alongside others, we will need an extended brand dimension. For this to be possible, we need to create a new table. To do this, we will use the following code to create a table
Brand All = Union( SUMMARIZE('Item','Item'[Brand], "All Brand", max('Item'[Brand])) ,
SUMMARIZE('Item','Item'[Brand], "All Brand", "Others")
)You can join it with the item dimension in the following way:
Under the modeling tab, you can create a new numeric parameter by clicking on the new parameter button
It can be used to control TOPN as well as others. It will create a new table
Parameter = GENERATESERIES(0, 20, 1)Create a measure “Brand Rank”
Rank Brand = rankx(ALLSELECTED('Brand All'[All Brand]), [Net])In order to be able to calculate the TOPN based on a parameter, we need a calculation that can give us that value
CALCULATE(sumx(filter(values( 'Brand All'[All Brand]) ,[Rank Brand] <= [Parameter Value]),[net]), 'Brand All'[All Brand] <> "Others")The final TOPN+Others measure will look like
Topn + Other =
var _nother = CALCULATE(sumx(filter(values( 'Brand All'[All Brand]) ,[Rank Brand] <= [Parameter Value]),[net]), 'Brand All'[All Brand] <> "Others")
return
if(ISINSCOPE('Brand All'[All Brand]), if([Rank Brand] <= [Parameter Value]+1, if(max('Brand All'[All Brand]) = "Others", [Net]- _nother,[Net]) , BLANK() ),[Net])Screenshots
You can find the file here.
You can find the video below
My Medium blog can be found here if you are interested
Click here to access all my blogs and videos in a jiffy via an exclusive glossary using Power BI. Please like, share, and comment on these blogs. I would appreciate your suggestions for improvement, challenges, and suggestions for topics so that I can explore these in more depth.
In addition, I have over 500 videos on my YouTube channel that are related to Power BI, Tableau, and Incorta. With the help of these videos, you will learn hundreds of DAX and Power Query functions, in addition to hundreds of Power BI, use cases that will assist you in becoming an expert in Power BI. Make sure you subscribe, like, and share it with your friends.
Master Power BI
Expertise in Power BI
Power BI For Tableau User
DAX for SQL Users
