topic Re: TOPN and Others with other columns as well in DAX Commands and Tips

TOPN and Others with other columns as well

azadthakur — Mon, 26 Aug 2024 08:40:24 GMT

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.

Re: TOPN and Others with other columns as well

bhanu_gautam — Mon, 26 Aug 2024 10:51:00 GMT

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
)

Re: TOPN and Others with other columns as well

azadthakur — Mon, 26 Aug 2024 10:58:11 GMT

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.