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
    • 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
    • Quick Measures Gallery
    • Dynamic ABC Classification

    Dynamic ABC Classification

    08-02-2018 10:32 AM

    Super User Greg_Deckler
    Super User
    11873 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    Dynamic ABC Classification

    ‎08-02-2018 10:32 AM

    This quick measure demonstrates how to implement ABC Classification as a measure. This allows for dynamic ABC classification that is contextually aware based upon filters. 

     

    The attached PBIX and report implements the ABC classification based upon columns as demonstrated here: https://www.daxpatterns.com/abc-classification/ for comparison.

     

    Note that the ABC classification for the column-based method is static even when the slicer is applied but the measure-based ABC classification is dynamic and takes the slicer filters into account. The measure formula is as follows:

     

     

    mABC Class = 
    VAR __salesTable = ADDCOLUMNS(ALLSELECTED('Sales SalesOrderDetail'),"__TotalSale",[OrderQty]*[UnitPrice])
    VAR __salesTable1 = GROUPBY(__salesTable,[ProductID],"__ProductSales",SUMX(CURRENTGROUP(),[__TotalSale]))
    VAR __salesTable2 = ADDCOLUMNS(__salesTable1,"__CumulatedSales",SUMX(FILTER(__salesTable1,[__ProductSales]>=EARLIER([__ProductSales])),[__ProductSales]))
    VAR __totalProductSales = SUMX(__salesTable1,[__ProductSales])
    VAR __salesTable3 = ADDCOLUMNS(__salesTable2,"__CumulatedPercentage",DIVIDE([__CumulatedSales],__totalProductSales,0))
    VAR __salesTable4 = ADDCOLUMNS(__salesTable3,"__ABC Class",SWITCH(TRUE(),[__CumulatedPercentage]<=0.7,"A",[__CumulatedPercentage]<=0.9,"B","C"))
    VAR __salesTable5 = FILTER(__salesTable4,[ProductID] = MAX('Production Product'[ProductID]))
    RETURN MAXX(__salesTable5,[__ABC Class])

     

    eyJrIjoiYzU2MDVjNDItMzQ0My00Y2RiLTkzNjUtNTNlMjFhMGU3OTkwIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9


    @ me in replies or I'll lose your thread!!!
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition
    Preview file
    28 KB
    DynamicsABC.pbix
    Labels:
    • Labels:
    • Other
    Message 1 of 7
    11,873 Views
    4
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    SamTaylor
    SamTaylor Helper I
    Helper I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎06-09-2022 09:46 AM

    Hi Greg,

    I am looing to try the Dynamic ABC Classification. My starting question is I have a table full of sales, but I only need to ABC for selection of the sales.  I have a Date Season and Product Season I will use to filter the data.  So when I create the first Var_SalesTable, how do I reduce the data selected to just the sales I need.  Cheers Sam

    Message 7 of 7
    589 Views
    0
    Reply
    gkaloferov
    gkaloferov
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎05-14-2021 05:05 AM

    @Greg_Deckler  Hi Greg, Did you manage to achieve the above-mentioned ABC technique by using Date slicer and second measure for sameperiodlastyear?

    For example,

     

    The ABCclass of "Product 123" in Year 2021/Quarter 1 was A

    The ABCclass of "Product 123" in Year 2020/Quarter 1 (SAME PERIOD LAST YEAR) was B

    and then both measures to be used simultaneously in a Table?

    Message 4 of 7
    4,791 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to gkaloferov
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎05-14-2021 06:22 AM

    @gkaloferov Not sure I entirely understand. There is a much improved version of this measure in my book, DAX Cookbook and you can find the chapter PBIX files on GitHub. First recipe in Chapter 12. And it would be unlike me to use SAMEPERIODLASTYEAR because I hate those functions. https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

     


    @ me in replies or I'll lose your thread!!!
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition
    Message 5 of 7
    4,780 Views
    0
    Reply
    gkaloferov
    gkaloferov
    Frequent Visitor
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎05-14-2021 08:27 AM

    @Greg_Deckler thank you for your time and for the answer. I recognize my mistake that I had to initially provide more information about the desired effect, but anyway, here's what I want to achieve as a result:

    I am trying to achieve а dynamic (responsive to date and other features) ABC customer classification together with time comparison indicators, namely, classification of customers based on the currently selected period but also I want to show in the second column of the table what was the class during the same period last year. I used the term SAMEPERIODLASTYEAR (not the function) only to represent the result I was looking for, without thinking that this was the right approach.

    If I have to be more detailed, I want to achieve something like a combination of your dynamic approach (which is much faster than that of SQLBI guys) and the snapshot approach of SQLBI, described here: https://www.daxpatterns.com/abc-classification/

    I looked at your example in github and I think that you really improved it a lot, but I can't figure out how to make it dynamic for more than 1 period.

    Something like this:
    pic1.png

    Regards,
    Georgi

    Message 6 of 7
    4,764 Views
    0
    Reply
    sirros_iot
    sirros_iot Helper IV
    Helper IV
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎07-15-2019 01:18 PM

    Nice report @Greg_Deckler !

    I would like to know if there's a way of counting the number os products on each classification to put in a overall table, in a dynamic way. Also, it would be awesome if we could filter the classification, selecting A, B or C. I'm having troubles with this topics.. 

    Best Regards,
    Diego S. Lutckmeier

    Message 2 of 7
    9,664 Views
    1
    Reply
    jskipper
    jskipper
    Frequent Visitor
    In response to sirros_iot
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎10-26-2020 04:54 AM

    I would also like to achieve this.

    Is it possible?

    Message 3 of 7
    6,310 Views
    0
    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