Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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"
)
)
Solved! Go to Solution.
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/
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/
Unreal! Worked perfectly. Thank you so much.
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.