cancel
Showing results 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

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?

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

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

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/

Frequent Visitor

Unreal! Worked perfectly. Thank you so much.

Frequent Visitor

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.

Super User

Hi,

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors