Search code examples
powerbitop-n

Applying top and bottom N in one Power BI visualization

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?

Solution

  • Here is the sample data I threw together.

    Data

    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.

    Filter

    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.

    Result