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
      • 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
    • Events
    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
    • Data Stories Gallery
    • Retail Analysis -Dynamic Measure&TopN Selection

    Retail Analysis -Dynamic Measure&TopN Selection

    01-31-2017 17:11 PM - last edited 02-02-2017 17:54 PM

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

    Retail Analysis -Dynamic Measure&TopN Selection

    ‎01-31-2017 05:11 PM

     This report is meant to highlight design techniques such as Dynamic titles, using custom tooltips, and key card layout. In addition, it allows the user to choose the KPI that matters the most to them (Sales, Returns, or Profits).  The report can be used to meet the needs of several end users.  Each end user has their own KPI’s that are most important to their role, and this method eliminates the need to create multiple reports for each department.

     

    Go To http://www.pandatagroup.com/blog or bipatterns.com for more techniques and user guides.

     

    If you have any questions for me, you can reach me via LinkedIn or in the PowerBI Community.

     

    I'm working on some blogs that outline some techniques for the Retail Analysis -Dynamic Measure&TopN Selection. If you take a look at it and see any part that you'd be interested in learning more about, I'd love to hear it so I can write a how-to post.  To start, I'll outline how to create the dynamic TopN part of this report.

     

    There’s no doubt PowerBI is a powerful reporting tool.  The method below outlines a pattern that can be used to meet the needs of several end users.  In order to create stunning Power BI reports that can allow a user to really dive into the data, you need to know DAX.  DAX will take your Power BI report to the next level, and the trick I’ll outline below is how to create a Dynamic TopN report.  It’s one thing to look at a KPI such as Sales company wide, but what if you want to identify the stores or items that are driving that KPI?

     https://community.powerbi.com/t5/Data-Stories-Gallery/Retail-Analysis-Dynamic-Measure-amp-TopN-Selection/m-p/121080https://community.powerbi.com/t5/Data-Stories-Gallery/Retail-Analysis-Dynamic-Measure-amp-TopN-Selection/m-p/121080https://community.powerbi.com/t5/Data-Stories-Gallery/Retail-Analysis-Dynamic-Measure-amp-TopN-Selection/m-p/121080

    View the report here: https://community.powerbi.com/t5/Data-Stories-Gallery/Retail-Analysis-Dynamic-Measure-amp-TopN-Selec...

     

    Power BI has a built in TopN function, but it isn't easy/intuitive for the user to select.  The TopN function in Power BI is great for a set-it-and-forget-it report, but not if you want to let the user choose the the 'N' value themselves.  You may want to see which stores or items are actually driving the KPI. In addition, you can use it to create the bottom half of the visual above I have there which is to see the TopN stores total contributition to the KPI, as well as Percent of total.  Sometimes it's just nice to have a button to press on the screen for the user rather than make them use the dropdowns on the side.

     

    TopN reports are where the users can choose to see the performance of the Top 5, Top 10, Top N… properties that contribute to a KPI.  The first step is to create a table that is disconnected from your data model (no relationships with any other tables).  This table, named “TopN” will look like below:

    Top

    5

    10

    15

    20

    30

    50

     

    This table and field will be used as a Slicer in the report, and the users selection will be captured using the following measure:

    SelectedTopNNumber = Min('TopN'[Top])

    This will return 5 by default, but you may want the default value to be something else.  In this case, you can write the following measure:

    SelectedTopNNumber = IF(HASONEVALUE('TopN'[Top]),Min('TopN'[Top]),10)

    The HASONEVALUE checks if the user has selected a value or not.  If they have, then use the number they’ve selected, otherwise set the default value to 10.

    Let’s say we’re looking to see the TopN contribution for Store’s.  We’ll use the following measure to determine the store’s rank.

    Store Rank By Selected Measure = IF(HASONEVALUE(DimStore[StoreName]),RANKX(ALLSELECTED(DimStore),[Selected Measure Value],,0))

    You can replace “Selected Measure Value” with the name of the measure you want to rank the store by.  We use ALLSELECTED() because we want the ranking to only be within the store’s that the user has selected.  For example, if the company has store’s in all 50 states, but the user has selected Wisconsin in a slicer, we want the measure to rank each store within just Wisconsin, not return the overall company ranking.

     

    Next, we need to determine if a given store should be included or not.  So we’ll add another measure:

    Should Store Be Included = IF([Store Rank By Selected Measure]<=[SelectedTopNNumber],1,0)

    Now, we can build a table on a Power BI report with the following fields and filters:

     TopN Table formatting.png

    And after adding TopN[Top] as a slicer, combined with some formatting, the end result is a report where the user can choose their own TopN value.

     

    If you have any questions for me, you can reach me via LinkedIn or in the PowerBI Community.

     

    I'm working on some blogs that outline some techniques for the Retail Analysis -Dynamic Measure&TopN Selection. If you take a look at it and see any part that you'd be interested in learning more about, I'd love to hear it so I can write a how-to post.  

    Also you can go to  bipatterns.com for more techniques and user guides.

     

     

    eyJrIjoiMGVhZGQzYTAtMmQzNy00YTFkLWE4NmQtM2UzNTk3ZWE5NWQ1IiwidCI6IjY3ZGQ1OGM3LTkwN2ItNGZkOC1hZWJmLTRhMTgzMWJmMjM5YSIsImMiOjN9

    Preview file
    394 KB
    Labels:
    • Labels:
    • Business
    • Economics
    • How To
    Message 1 of 10
    17,297 Views
    10
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    TVNRaju
    TVNRaju
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-09-2022 04:14 AM

    Super!!!

    Can you please send me the .pbix file for this report.

    Email: rajutvn1991@gmail.com

    Message 10 of 10
    254 Views
    0
    Reply
    Timmycoolguy
    Timmycoolguy
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-14-2022 08:49 PM

    Hi, any chance if you plz share me the pbix on 936272126@qq.com ? 

    Message 9 of 10
    1,327 Views
    0
    Reply
    jxv5016
    jxv5016
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-23-2019 10:55 AM

    Hello, Any chance that the PBIX file can be shared? I am new to Power BI and trying to learn new things!

    Message 8 of 10
    8,467 Views
    0
    Reply
    mdaamirkhan
    mdaamirkhan Post Prodigy
    Post Prodigy
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-22-2018 02:41 AM

    can you share me the pbix file.

    Message 7 of 10
    11,399 Views
    1
    Reply
    SEBot
    SEBot
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-20-2017 09:57 AM

    After the TopN is selected by the user, is it possible to show the sum of all the other stores? I'm guessing the DAX would need to be updated in the "Should Store Be Included" measure? 

    Message 6 of 10
    15,056 Views
    0
    Reply
    Fitin1rb
    Fitin1rb Helper III
    Helper III
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-08-2017 06:42 AM

    @Anonymous this is great!  I have a question regarding the Donut Chart and the Card below the list.  How do you make those dynamic show the $ and % that the TopN Slicer consists of?  I am having issues with those working for me.

     

     

    Thanks!

    Message 2 of 10
    16,608 Views
    0
    Reply
    Anonymous
    Not applicable
    In response to Fitin1rb
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-09-2017 10:58 AM

    @Fitin1rb

    Create a measure like Dollar Sign="$" and then use a key card.

     

    If you have any questions for me, you can reach me via LinkedIn or in the PowerBI Community.

    Message 3 of 10
    16,595 Views
    0
    Reply
    Anuar
    Anuar
    New Member
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-27-2017 02:07 PM

    Hi, I have the same problem, could you help me?

     

    Thanks a lot!!!

    Message 5 of 10
    16,123 Views
    0
    Reply
    Anuar
    Anuar
    New Member
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-27-2017 02:05 PM

    Hi, I have the same problem, could you help me?

     

    Thanks a lot!!!

    Message 4 of 10
    16,123 Views
    0
    Reply

    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
    California Consumer Privacy Act (CCPA) Opt-Out Icon Your California Privacy Choices