Forum Discussion
Dynamic Table Top10 Grouping & Other
- 2 years ago
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 _ResultPut 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/