Top 5 and Bottom 5 Product within each categories

Power BI
6

@ajinkyakadam0009,

Give this a go:

Top 5 by Category =

VAR SalesThisProduct = [Total Sales]
VAR RankingTopDown =
    CALCULATE (
        COUNTROWS (
            FILTER (
                ALLSELECTED ( Products[Product Name] ),
                [Total Sales] >= SalesThisProduct
            )
        ),
        ALLEXCEPT ( Products, Products[Product Category] )
    )
VAR Top5 =
    IF ( RankingTopDown <= 5, RankingTopDown, BLANK () )
RETURN
    Top5

Bottom 5 is a little more complicated:

Bottom 5 by Category =

VAR SalesThisProduct = [Total Sales]
VAR AllRows =
    CALCULATE ( COUNTROWS ( Products ), ALL ( Products ) )
VAR RankingBottomUp =
    CALCULATE (
        COUNTROWS (
            FILTER (
                ALLSELECTED ( Products[Product Name] ),
                [Total Sales] <= SalesThisProduct
            )
        ),
        ALLEXCEPT ( Products, Products[Product Category] )
    )
VAR MinRankByCategory =
    CALCULATE (
        MINX ( Products, [Rank by Category] ),
        ALLEXCEPT ( Products, Products[Product Category] )
    )
VAR AdjRank = RankingBottomUp - MinRankByCategory + 1
VAR Bottom5 =
    IF ( AdjRank <= 5, AdjRank, BLANK () )
RETURN
    Bottom5

Here it is all put together:

image
image546×543 16.5 KB

I hope this is helpful. Full solution file attached.

1 Like