skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led training
    • Getting started
      • Overview
      • Online workshops
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power BI Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish Community
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • Power BI- TOPN with Others

    Power BI- TOPN with Others

    12-10-2022 22:07 PM

    Super User amitchandak
    Super User
    730 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    amitchandak
    Super User amitchandak
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Power BI- TOPN with Others

    ‎12-10-2022 10:07 PM

    Power BI TOPN + Others

    Power BI(22).jpg

     

    Power Bi TOPN with 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:

    amitchandak_1-1670738597019.png

     

    Under the modeling tab, you can create a new numeric parameter by clicking on the new parameter button

    amitchandak_2-1670738597149.png

     

    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

    amitchandak_3-1670738597180.png

     

    amitchandak_4-1670738597736.png

     

    You can find the file attached below

    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



    !! Microsoft Fabric !!
    Microsoft Power BI Learning Resources, 2023 !!
    Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
    Rank with Others.pbix
    Labels:
    • Labels:
    • Filters
    • Financial
    • Mathematical
    • Other
    • Totals
    Message 1 of 1
    730 Views
    1
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices