The missing information on Top N lists

Today I have been working on a somewhat standard view: the creation of a Top-N type matrix with some additional information. Typically this focuses on the customers / products / some other attribute that have the top 10 actual sales year to date. This figure is often shown and compared with e.g. last year sales and budget. Apart from the ever going discussion on top 10 or 20 display, this is a quick-win visual as it can easily be done with a Top-N visual filter. Nothing fancy.

That said; I think there is much more potential to such a dedicated view than you would think of initially. In the text below I cover two items that are often times missing in action according to me.

MIA 1: How bright are your star players actually shining?

Often times, Top N tables are sorted on the value they are accounted for, and a nice total row below the table is added. However, apart from being the total of Top N items, what does this tell about how big of a share this is of your total portfolio? Also how important is this #1 item really for your business? Knowing that your Top-N items make up a big chunk of your yearly total is vital information in some cases.

This Top 10 account example, shows per item the % of total sales. The first four items are really important, and the top 10 items apparently make up 39% of your portfolio. If you know you have 2000 items.... Better guard these items carefully.

The realization is straightforward with some DAX:

In case you don't do the CustomerTable[Account] filter, the total will show 100% and per item only the relative % of contribution to the top 10 items total is shown.

MIA 2: You are missing out on dynamic information

While building the Top 15 table that I was making I realized all of a sudden that I had no idea how the star players on it entered the list. Also I wondered: what if last year I had a number 1 super item, that was this year all of a sudden no longer on the Top 15 list? Would this not be very important information to know? Or what if an item that was last year not even in the Top 200 would now all of a sudden appear in the Top 15: would this not be a very important piece of intelligence showing a rising player?

This dynamic piece of information was not covered anywhere in the current view and I wondered if I could change this. The resulting table is shown below. Note the annoying 'customer' texts but of course these are just dummies for the sake of the example.

This table is shown together with (or hidden behind a bookmark) a Top 15 table.

It shows all accounts that are either this year in the top 15, or have been in the top 15 last year. The current ranking is given, as well as the relative movement up (green) or down (red) the ladder.

Interesting especially are the rise of customers 232 and 987, and the downfall of customer 232. The table is also fully responsive to any slicers set.

The realization in DAX is a bit more elaborate in this case. The table consists of an account column, and two measures. To keep the article short I will focus on the measure [▼▲] that creates the rightmost column of the above table only. The measure [#] is more or less created in the same fashion - as will be explained later.

DAX part 1: Ranking of sales

I must admit; the ranking formula still manages to puzzle me at times. Note we don't do the filtering within the calculate statement on account here. It won't work. Also note that if you set a visual filter, the ranking is influenced.

In the example below, a standard sales ranking per account is given. For our DAX formula however we need two rankings as you will see later. These can either be put in a measure or in a VAR: one for previous year ranking and one for actual year ranking.

DAX part 2: Determining what to show

The next piece is only to get some helper variables. With the MAXn variable you can set the maximum number of items in scope.

DAX part 3: the output

All the above was a preparation on the eventual output. Note that you can also add the current ranking of course here by just adding rank_ac to the output. I chose to put this in a separate measure, which is exactly the same in setup but with an output the rank_ac variable.

Note that i still use blank() as output in that case; as than the matrix will not show any other than the intended items - without having to filter on the visual which would introduce new context problems. Also note that the coloring was done by creating yet another measure that is virtually identical. Only this time, i replaced all downward outputs with -1, the "-" with 0 and the upward items with a 1 and then used conditional formatting using this field. Too bad we can't conditionally format on text which would save this step. Of course I also realize that these three measures can be made more efficient; for example by having only one measure that outputs various state-id's and then the other measures could simply use that state to define their outputs without having to do the full DAX for each measure.

The complete DAX

Sorry, not commented this time. But all is above :)

Conclusion

Wow. You actually made it to the end. What is your opinion on this matter? Do you have additional examples or use cases on what to do in relation to TOP-N lists? Please let me know in the comments and feel free to ask any questions

Dennis Priester