cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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

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.

Frequent Visitor

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.

2 REPLIES 2
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:

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.

Frequent Visitor

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.

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors