powerbidaxgreatest-n-per-groupcolumn-chart

Power BI: Column chart with both top and bottom


I'm trying to get in the same bar chart both the highest TOPN and the bottom TOPN in a way that resembles this graph:

enter image description here

But so far, no luck. I'm using DIM_Brand'[Brand] as category and FACT_Project_Portfolio[On-Shelf (days)] as values. My first reasoning was to create both measures seperately and add them to my chart. As an example for highest topN:

Top_4_Brands_On_Shelf_Days = 
VAR Top_4_Brands = TOPN(
    4, 
    SUMMARIZE(
        'DIM_Brand', 
        'DIM_Brand'[Brand],
        "Total On-Shelf Days", SUM(FACT_Project_Portfolio[On-Shelf (days)])
    ),
    [Total On-Shelf Days], DESC
)
RETURN
    SUMX(Top_4_Brands, [Total On-Shelf Days])

But not only I can't bring both measures on the same Y-axis; it doesn't even return the TOP4 by itself (while it works properly if I use the same DAX code to create a new table, so I guess I'll have to make use of that).


Solution

  • I ranked all your brands based on the quantity, both from highest to lowest and lowest to highest.

    I filtered this ranked list to include only the top 3 and bottom 3 brands.

    Then I combine these subsets into a new table.

    TopBottom3Brands = 
    VAR Top3 = TOPN(3, ALL('MyTable'), 'MyTable'[Quantity], DESC)
    VAR Bottom3 = TOPN(3, ALL('MyTable'), 'MyTable'[Quantity], ASC)
    RETURN
    UNION(Top3, Bottom3)
    

    Here is my sample data :

    enter image description here

    enter image description here

    enter image description here