Applying top and bottom N in one Power BI visualization

Question

Charts and visuals on Power BI can be filtered to the top n records for easier at-a-glance reporting. However, if I want to report the top and bottom records (eg: top 10 and bottom 10 dollar amounts) I need to place two visuals. This consumes more report space and breaks the cohesion of the page.

Is there a way to select two data subsets in one graph for this kind of reporting?

score 4 · Accepted Answer · answered Mar 19 '19 at 20:42

Here is the sample data I threw together.

On top of that, I created a simple measure for the Total Amount.

Total Amount = SUM(Data[Amount])

With that I created a new measure that will essentially flag each row as being in the Top or Bottom 3 (you can change the number to meet your needs).

This measure first checks if there is a value for Total Amount and "removes" any that have a blank value ("removes" by making the flag blank and thus will never be included in any filtering or such).

TopBottom = 
    IF(
        ISBLANK([Total Amount]),
        BLANK(),
        IF(
            RANKX(ALL(Data), [Total Amount], , ASC) <= 3 || RANKX(ALL(Data), [Total Amount], , DESC) <= 3,
            1,
            0
        )
    )

Once you have the ranking flag measure, you can add it to your visual and then filter to where the measure is 1.

Once that is all finished, you should have a visual only showing the entries you care about. Here is the full list of data with the flag visible and the resulting table when applying the filter.

I didn't know RANKX() is a clause in Power BI. Cery clever, +1 — PausePause, Mar 19 '19 at 21:00

score 0 · Answer 2 · answered Apr 18 '20 at 02:17

Consider this as a slight improvement to the accepted answer. With this one, you don't need to change the formula whenever you want to change how many you want to see.

The only control you will need to change is the filter.

RankTopBottom = RANKX(ALL(Data), [Total Amount], , ASC) * 
                RANKX(ALL(Data), [Total Amount], , DESC)

It uses basically the same principle of the accepted answer, but instead of using an IF, we multiply both rankings. The smallest values will be the edges, the gratest values will be the middle.

So, when filtering, use "bottom" N, and pick an even number. (Or add a negative sign if you want the "top" N instead)

A quick result of multiplying inverse ranks:

Applying top and bottom N in one Power BI visualization

2 Answers2