Power BI and Azure Data Platform

Dynamic ABC Classification DAX pattern

ABC analysis is an inventory categorization technique, with conceptual similiarity to Pareto analysis - see Wikipedia article

Understanding of these three DAX patterns is a prerequisite:

• Dynamic Segmentation
• Static ABC Classification
• Parameter Table

Dynamic ABC Classification as explained on Exceed.hr site in 2018 uses DAX variables to improve the solution by Gerhard Bruekl published in 2015, explained on DAX patterns site.

• Basic pattern example

  This uses several calculated columns in the Products table.

• Complete pattern example

  ABC Granular = CALCULATE ( [TotalRevenue],
      VALUES ( Dim_product[Product] ),
      FILTER (
          CALCULATETABLE (
              VAR BasicTable =
                  ADDCOLUMNS ( VALUES ( Dim_product[Product] ), "ProductRevenue", [TotalRevenue] )
              RETURN
                  ADDCOLUMNS (
                      BasicTable,
                      "Cumulative%", DIVIDE (
                          SUMX (
                              VAR CurrentProductRevenue = [ProductRevenue]
                              RETURN
                                  FILTER ( BasicTable, [ProductRevenue] >= CurrentProductRevenue ),
                              [ProductRevenue]
                          ),
                          CALCULATE ( [TotalRevenue], VALUES ( Dim_product[Product] ) )
                      )
                  ),
              ALL ( Dim_product[Product] )
          ),
          [Cumulative%] > [MinBoundary]
              && [Cumulative%] <= [MaxBoundary]
      )
  )
  

Return to:

Top

Static Segmentation DAX pattern

Dynamic Segmentation DAX pattern

Parameter Table DAX pattern

Back to overview of DAX patterns

Home