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
    • Office 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
    • 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
    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
    • Top 1 per category

    Top 1 per category

    08-04-2017 03:51 AM - last edited 08-04-2017 03:54 AM

    Anonymous
    Not applicable
    29948 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    Top 1 per category

    ‎08-04-2017 03:51 AM

     

    Sometimes it can be valuable to simply show what the top performing products or customers are. The TopN filter in Power BI makes this an easy task but is bounded to a visual. To get the best performing category in a single measure, this Quick calculation can be used.

     

    In the pbix, use the relative date slicer to see the top sold product changing.

     

    How it works

    Per category the selected measure is calculated, the TopN part will only select the first row based on the descending order for the selected measure over the categories. When 2 rows in the categories share the same value, the lastnonblank selects the latest value based on the column sorting order. This can be changed to firstnonblank to select the first.

     

    Examples:

     

    Top 1 Productname for Sales =
    CALCULATE (
    LASTNONBLANK ( 'Product'[Productname]; 1 );
    FILTER ( TOPN ( 1; VALUES ( 'Product'[Productname] ); [Sales]; DESC ); TRUE () )
    ) 

     

    To get the value for the top 1 category, replace the LASTNONBLANK() with the {Base value}

     

     

    Sales for Top 1 productname =
    CALCULATE (
    [Sales];
    FILTER ( TOPN ( 1; VALUES ( 'Product'[Productname] ); [Sales]; DESC ); TRUE () )
    ) 

     

    To get the friendly label simply use:

     

    Best product label =
    "The best sold product is " & [Top 1 Productname for Sales] & " with " & FORMAT([Sales for Top 1 productname];"€ #,#") &" in sales"

     

     

    NAME:

    Top 1 per category

     

    DESCRIPTION:

    Calculates the top 1 of the base value over the category

     

    PARAMETERS:

    Name: Base value

    Tooltip: The value that is used to determine the top 1

    Type: Numerical field / measure

     

    Name: Category

    Tooltip: The category in which you want to calculate the top 1

    Type: Categorical field

     

    DAX:

     

    Top 1 {Category} for {Base value} =
    CALCULATE (
    LASTNONBLANK ( {Category}; 1 );
    FILTER ( TOPN ( 1; VALUES ( {Category} ); {Base value}; DESC ); TRUE () )
    )

     

     

     

     

    eyJrIjoiN2RmOWQ3MzctNDIwMC00N2U3LTk3ZjUtMTM3M2ZhYTI0N2NkIiwidCI6IjY2MjU3MWNmLWM5ZjgtNGQyOS1hYzcxLTA2N2E0NTY4MDExZCIsImMiOjh9

    Preview file
    22 KB
    Top 1 per category.pbix
    Labels:
    • Labels:
    • Filters
    Message 1 of 4
    29,948 Views
    18
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    srirambalan
    srirambalan
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎07-15-2020 06:10 AM

    HI, I found this very useful - considering that i am an amateur, i am struggling to replicate the same for finding the TOP VALUE in the Subcategory, under the category determined as TOP category.

     

    e.g. if the BASE VALUE is Total Sale -  Sum(Sale Value) 

     

    Brand A    - $15000

    - Cat A         -  $11000 

      SubCat 1     - $3000

      SubCat 2     - $2000

      SubCat 3    - $6000

     

    - Cat B         -  $4000

       Subcat 1 - $2500    

       Subcat 3 - $1500

    Let's assume Brand A is the TOP 1 basis the Sale Value in Brands, then:

     - under BRAND A,  CAT A is the TOP 1 category value
    - Under Cat A, SubCat 3 is the TOP 1 Sub category value

     

    How can i achieve this, so that i can view this in a matrix or table visual ? Note that the table visual would list the following
    - Customer ID

    - Total Sale Value of the customer

    - Customer TOP Brand

    - Total Sale value of the TOP Brand

    - Customer TOP Category

    - Total Sale Value of the TOP category
    - Customer TOP Sub Category
    - Total Sale Value of the TOP category

    I have a single fact table which features all the above.

    Message 4 of 4
    13,899 Views
    0
    Reply
    mdaamirkhan
    mdaamirkhan Post Prodigy
    Post Prodigy
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎03-14-2018 09:32 PM

    Hello

     

    How will I defined two product name in the following wuery that you are given.

    Message 2 of 4
    28,104 Views
    0
    Reply
    Anonymous
    Not applicable
    In response to mdaamirkhan
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎03-16-2018 01:51 AM

    Hi @mdaamirkhan

     

    What do you want to accomplish? The names of the 2 products that have the highest sales?

     

    Because it's a measure you cannot return 2 rows. You should either contatenate the rows or use a table visual to filter on the top 2 based on sales. You could also create a New table with DAX where you can use the below FILTER() statement to reutn the top 2 products.

     

    To concatenate the top 2 products you can use the formula below.

     

    Top2Concatenated =
    CONCATENATEX (
        TOPN ( 2; VALUES ( 'Product'[Productname] ); [Sales]; DESC );
        'Product'[Productname];
        ". "
    )

    Message 3 of 4
    28,047 Views
    2
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • 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

    © 2022 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks