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
kemppaik
Frequent Visitor

Dynamic Ranking of Top 10 and Others in Matrix

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

image.png


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.

2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User
Super User

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:

TopNSort.jpg

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.

Top5MeasureHightlighted.jpg

View solution in original post

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.

 

View solution in original post

3 REPLIES 3
Rocky2121
Frequent Visitor

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

jdbuchanan71
Super User
Super User

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:

TopNSort.jpg

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.

Top5MeasureHightlighted.jpg

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.

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.