TOPN and Others with other columns as well

[azadthakur]

Hello Team,

I’ve successfully created a measure to calculate the Top N brands by sales and the "Others" category based on a Top N slicer. However, I've encountered an issue: when I add another column, such as "Year," to the matrix visual, the Top N brands vary across different years. I need the Top N brands to remain consistent across all years, regardless of the year filter.

Top N Retail Sales or Units =

VAR TopNValue1 = SELECTEDVALUE('TopN'[TopN])

VAR SalesorUnits = SELECTEDVALUE('Retail Sales or Units'[Metric])

VAR TopBrand =

    TOPN(

        TopNValue1,

        ALLSELECTED(TopNBrands),

        SWITCH (

            SalesorUnits,

            "Units", [Sum of Units],

            "Sales", [Sum of Sales],

            BLANK ()

        )

    )

VAR AllRetailSalesUnits =

    CALCULATE(

        SWITCH (

            SalesorUnits,

            "Units", [Sum of Units],

            "Sales", [Sum of Sales],

            BLANK ()

        ),

        ALLSELECTED(TopNBrands)

    )

VAR OtherRetailSalesUnits =

    AllRetailSalesUnits -

    CALCULATE(

        SWITCH (

            SalesorUnits,

            "Units", [Sum of Units],

            "Sales", [Sum of Sales],

            BLANK ()

        ),

        TopBrand

    )

VAR TopNRetailsSalesUnits =

    CALCULATE(

        SWITCH (

            SalesorUnits,

            "Units", [Sum of Units],

            "Sales", [Sum of Sales],

            BLANK ()

        ),

        KEEPFILTERS(TopBrand)

    )

VAR CurrentBrand = SELECTEDVALUE(TopNBrands[Brand])

RETURN

    IF(

        CurrentBrand = "Other",

        OtherRetailSalesUnits,

        TopNRetailsSalesUnits

    )" I am getting below result when i am selecting topn as 5 in slicer with onl brands

but when i drag fiscal year also here, it is giving me different brands across each year like below"

and i need same topn brands across any year, it should not be different top brands for every year.

[bhanu_gautam]

@azadthakur , To ensure that the Top N brands remain consistent across all years, you need to modify your DAX measure to calculate the Top N brands without considering the year filter. This can be achieved by using the ALL function 

Top N Retail Sales or Units =
VAR TopNValue1 = SELECTEDVALUE('TopN'[TopN])
VAR SalesorUnits = SELECTEDVALUE('Retail Sales or Units'[Metric])

-- Calculate the Top N brands without considering the year filter
VAR TopBrand =
TOPN(
TopNValue1,
ALL(TopNBrands),
SWITCH (
SalesorUnits,
"Units", [Sum of Units],
"Sales", [Sum of Sales],
BLANK ()
)
)

-- Calculate the total sales or units for all brands without considering the year filter
VAR AllRetailSalesUnits =
CALCULATE(
SWITCH (
SalesorUnits,
"Units", [Sum of Units],
"Sales", [Sum of Sales],
BLANK ()
),
ALL(TopNBrands)
)

-- Calculate the sales or units for the "Other" category
VAR OtherRetailSalesUnits =
AllRetailSalesUnits -
CALCULATE(
SWITCH (
SalesorUnits,
"Units", [Sum of Units],
"Sales", [Sum of Sales],
BLANK ()
),
TopBrand
)

-- Calculate the sales or units for the Top N brands
VAR TopNRetailsSalesUnits =
CALCULATE(
SWITCH (
SalesorUnits,
"Units", [Sum of Units],
"Sales", [Sum of Sales],
BLANK ()
),
KEEPFILTERS(TopBrand)
)

-- Get the current brand
VAR CurrentBrand = SELECTEDVALUE(TopNBrands[Brand])

RETURN
IF(
CurrentBrand = "Other",
OtherRetailSalesUnits,
TopNRetailsSalesUnits
)

[azadthakur]

Hi @bhanu_gautam , thanks for replying on this. My fiscal year column is in different table. I tried your formula but result is same the topn brands is different for each year.