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 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"
)
)


Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    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/

     

     

     

5 Replies

  • Rocky2121's avatar
    Rocky2121
    Frequent Visitor
    2 years ago

    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.

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    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/

     

     

     

    • Rocky2121's avatar
      Rocky2121
      Frequent Visitor
      2 years ago

      Unreal! Worked perfectly. Thank you so much.