Forum Discussion

Rocky2121's avatar
Rocky2121
Frequent Visitor
2 years ago
Solved

Dynamic Table Top10 Grouping & Other

I'm seeking assistance with creating a Dynamic column or table that would allow me to display customers in a table in 2 different groups. Top 10 and Other.   The goal is to categorize customers bas...
  • jdbuchanan71's avatar
    2 years ago

    Rocky2121 

    I think this can be solved by treating it more like a dynamic ABC classification.

    We need a table to hold the customer segments.  You can create it with this dax code.  I did top 10 just so the screen shots would fit better.

     

    Customer Segments = 
    DATATABLE (
        "Segment", STRING,
        "Order", INTEGER,
        "Rank Start", INTEGER,
        "Rank End", INTEGER,
        {
            { "Top 30", 1, 1, 10 },
            { "Other", 2, 11, 9999999 }
        }
    )

     

     

    Then we need a ranking measure to set the rank for the cusomters.  This will be used to put them in the correct segment.  I am basing mine of [Sales].

     

    Customer Rank = 
    RANKX ( ALLSELECTED ( Customer ), [Sales] )

     

     

    And a measure to calculate the [Sales] for the customers in the Segments.

     

    Customer Segment Sales = 
    VAR _CustomersInSegment = 
        FILTER (
            ALLSELECTED ( Customer ),
            VAR _RankOfCustomer = [Customer Rank]
            VAR _SegmentForCustomer =
                FILTER (
                    'Customer Segments',
                    NOT ISBLANK ( _RankOfCustomer )
                        && 'Customer Segments'[Rank Start] <= _RankOfCustomer
                        && 'Customer Segments'[Rank End] >= _RankOfCustomer
                )
            VAR _IsCustomerInSegment =
                NOT ISEMPTY ( _SegmentForCustomer )
            RETURN
                _IsCustomerInSegment
        )
    VAR _Result =
        CALCULATE ( [Sales], KEEPFILTERS ( _CustomersInSegment ) )
    RETURN
        _Result

     

     

    Put the Segment from the Customer Segments table and the customer name in a matrix along with the [Customer Segment Sales] measure:

     

    And this will adjust as you make selections:

     

    I have attached my sample file for you to look at.

     

    This is based on this pattern from SQLBI.
    https://www.daxpatterns.com/abc-classification/