Dynamic Table Top10 Grouping & Other

[Rocky2121]

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 based on their total accounts receivable amounts. I need to calculate the total accounts receivable amount for each customer. Next, I aim to identify the top 10 customers with the highest total amounts and label them as 'Top 10'. The remaining customers should be labeled as 'Other'. 

I created the following table and made the relationship to my main AR Table. It works but when I add a country filter, it will only include them in the Top 10 grouping, if they are in the total Top 10 in my full ARTable. Is there any way I can add some logic in my below table so if I filter by country or city, it will dynamically change the rankings based on whatever filter I add? 

TopNTable =
VAR Top10Customers = TOPN(10,
SUMMARIZE(
ARtable,
ARtable[DEBTOR],
"TotalAmount", SUM(ARtable[Total_AR])
),
[TotalAmount], DESC
)
VAR OtherCustomers = EXCEPT(
SUMMARIZE(ARtable, ARtable[DEBTOR]),
SUMMARIZE(Top10Customers, [DEBTOR])
)
RETURN
UNION(
SELECTCOLUMNS(
Top10Customers,
"Customer", ARtable[DEBTOR],
"Category", "Top 10"
),
SELECTCOLUMNS(
OtherCustomers,
"Customer", ARtable[DEBTOR],
"Category", "Other"
)
)

[jdbuchanan71 · Accepted solution]

@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/

[Rocky2121]

Unreal! Worked perfectly. Thank you so much. 

[Rocky2121]

My desired result is to have a matrix table and have the customers listed under "top" and "other". By creating the table I described above, there is no way for this to be dynamic when I filter by country. The abobe reccomendations are great and I understand created tables and columns do not work like calculated measures. I don't think either of the above solutions will solve my desired results but I appreicate you sharing.

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached PBI file.

Hope this helps.

[lbendlin]

Filtering the top products alongside the other products in Power BI - SQLBI