Dynamic Ranking of Top 10 and Others in Matrix

[kemppaik]

Hello,

I’m trying to generating a listing of the Top 10 customers for a selected time range, in addition to a total for “Others”.

The time range is a slicer and filters the data.

I have been able to get to this point:

Group	Customer Name	January	February	March	Total
Top 10	Customer 1	1000	2000	3000	6000
	Customer 2	990	1980	2970	5940
	Customer 3	980	1960	2940	5880
	Customer 4	970	1940	2910	5820
	Customer 5	960	1920	2880	5760
	Customer 6	950	1900	2850	5700
	Customer 7	940	1880	2820	5640
	Customer 8	930	1860	2790	5580
	Customer 9	920	1840	2760	5520
	Customer 10	910	1820	2730	5460
	Total	9890	19840	29790	59520
Others	Other Customer 1	100	200	300	600
	Other Customer 2	90	190	290	570
	Other Customer 3	80	180	280	540
	Other Customer 4	70	170	270	510
	Total
Total	Total	9890	19840	29790	59520

I have a “Groups” table as follows (it is not linked to my other table):

The Group column is what appears in my current matrix.

Customer Name is from my Details table.
The date columns are the Year and Month selected in the slicer.

The values are a measure I created as follows:

Customer Sales (All) =

VAR OverallRanking = [Customer Ranking]

RETURN

CALCULATE( [TotalSales],

    FILTER( VALUES( Details[Customer Name] ),

        COUNTROWS(

            FILTER( 'Groups',

            OverallRanking > 'Groups'[Min]

            && OverallRanking <= Groups[Max] ) )

        > 0 ))

Customer Ranking is:

Customer Ranking =

    CALCULATE(

        RANKX( ALL( Details[Customer Name] ), [TotalSales], , DESC ),

            ALL( Details[YearMonth] ) )

While this gets me mostly where I want to get to, there are problems with what is listed above:

• The Totals are incorrect
• Group column should not be displayed in the final output
• “Others” should appear as a single row directly under the top 10 customers

This is what my desired output is:

Customer Name	January	February	March	Total
Customer 1	1000	2000	3000	6000
Customer 2	990	1980	2970	5940
Customer 3	980	1960	2940	5880
Customer 4	970	1940	2910	5820
Customer 5	960	1920	2880	5760
Customer 6	950	1900	2850	5700
Customer 7	940	1880	2820	5640
Customer 8	930	1860	2790	5580
Customer 9	920	1840	2760	5520
Customer 10	910	1820	2730	5460
Others	340	740	1140	2220
Total	9890	19840	29790	59520

How can I get to my desired output?

Thanks.

[jdbuchanan71 · Accepted solution]

Hello @kemppaik 

I have used the following method in a couple of models with success.

First, we need  table that has the unique list of items you want to rank and an additional row for "Other", we can get that with a simple calculated table.

Customers = 
UNION (
    DISTINCT ( 'Details'[Customer Name] ),
    ROW ( "Customer Name", "Other" )
)

This table we join back into Details on the [Customer Name] field.

Then we can write the measure that calcs our TopN customers and other. 

Top N = 
VAR Top_N =
    CALCULATETABLE ( Customers, TOPN ( 5, ALL ( Customers ), CALCULATE ( [Total Amount], ALL ( 'Date' ) ) ) )
RETURN
    IF ( 
        NOT ISFILTERED ( Customers[Customer Name] ), CALCULATE ( [Total Amount], ALL ( Customers ) ),
        IF ( SELECTEDVALUE ( Customers[Customer Name] ) = "Other",
            CALCULATE ( [Total Amount], EXCEPT ( ALL ( Customers ), Top_N ) ),
            CALCULATE ( [Total Amount], INTERSECT ( Customers, Top_N ) )
        )
    )

Finally we need a measure to do the sorting of our customers since we want the Top customers sorted descending the other on the bottom.

TopN Sort = 
IF ( SELECTEDVALUE ( Customers[Customer Name] ) = "Other", 0, [Top N] )

The sorting part is a bit ugly but it is the only way I know to do it right now.  We add the TopN Sort measure into the matrix, sort the matrix by TopN Sort and collapse all the columns of TopN Sort so you don't see them.  This is all so we get the following:

I have uploaded my sample .pbix file here Top 5 Other sorted.pbix

In my example I only did the top 5, you just need to change the highlighted number.

[Rocky2121]

Any result? I am trying to do a simular approach? Re: Dynamic Table Top10 Grouping & Other - Microsoft Fabric Community

[jdbuchanan71 · Accepted solution]

Hello @kemppaik 

I have used the following method in a couple of models with success.

First, we need  table that has the unique list of items you want to rank and an additional row for "Other", we can get that with a simple calculated table.

Customers = 
UNION (
    DISTINCT ( 'Details'[Customer Name] ),
    ROW ( "Customer Name", "Other" )
)

This table we join back into Details on the [Customer Name] field.

Then we can write the measure that calcs our TopN customers and other. 

Top N = 
VAR Top_N =
    CALCULATETABLE ( Customers, TOPN ( 5, ALL ( Customers ), CALCULATE ( [Total Amount], ALL ( 'Date' ) ) ) )
RETURN
    IF ( 
        NOT ISFILTERED ( Customers[Customer Name] ), CALCULATE ( [Total Amount], ALL ( Customers ) ),
        IF ( SELECTEDVALUE ( Customers[Customer Name] ) = "Other",
            CALCULATE ( [Total Amount], EXCEPT ( ALL ( Customers ), Top_N ) ),
            CALCULATE ( [Total Amount], INTERSECT ( Customers, Top_N ) )
        )
    )

Finally we need a measure to do the sorting of our customers since we want the Top customers sorted descending the other on the bottom.

TopN Sort = 
IF ( SELECTEDVALUE ( Customers[Customer Name] ) = "Other", 0, [Top N] )

The sorting part is a bit ugly but it is the only way I know to do it right now.  We add the TopN Sort measure into the matrix, sort the matrix by TopN Sort and collapse all the columns of TopN Sort so you don't see them.  This is all so we get the following:

I have uploaded my sample .pbix file here Top 5 Other sorted.pbix

In my example I only did the top 5, you just need to change the highlighted number.

[kemppaik · Accepted solution]

Thanks for the response @jdbuchanan71 .  I was able to get this to work.  I tried a Top N calculation before but didn't get this far because I didn't do the Top N calculation correctly - I was making it too complex.