Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Rocky2121
Frequent Visitor

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? 

 

Rocky2121_0-1713910284932.png

 


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


1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@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 }
    }
)

 

jdbuchanan71_7-1714077127449.png

 

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:

jdbuchanan71_5-1714076988254.png

 

And this will adjust as you make selections:

jdbuchanan71_6-1714077007863.png

 

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/

 

 

 

View solution in original post

5 REPLIES 5
jdbuchanan71
Super User
Super User

@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 }
    }
)

 

jdbuchanan71_7-1714077127449.png

 

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:

jdbuchanan71_5-1714076988254.png

 

And this will adjust as you make selections:

jdbuchanan71_6-1714077007863.png

 

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/

 

 

 

Unreal! Worked perfectly. Thank you so much. 

Rocky2121
Frequent Visitor

Rocky2121_0-1714011280747.png

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
Super User
Super User

Hi,

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.