How to get TopN+OurProducts, and the rest grouped as others. [Read Below for more info]

[Afthab]

So, I have been able to get the TopN products from list of products, and the rest as others. I created a new table from my main table and made a 1:Many relationship with it. Code for the new table:

TopProducts = UNION(VALUES('LPO Combined Molecule Mo (2)'[Product]), ROW("Product", "Other"))

new measure that shows opN products from list of products, and the rest as others:

Top N Value = 
Var TopNValue = 5

Var TopPProduct=
	TopN(TopNValue, ALLSELECTED(TopProducts), 'LPO Combined Molecule Mo (2)'[MAT 2023 Value SUM])

Var AllValue = CALCULATE('LPO Combined Molecule Mo (2)'[MAT 2023 Value SUM], ALLSELECTED(TopProducts))

Var OtherValue = AllValue - CALCULATE('LPO Combined Molecule Mo (2)'[MAT 2023 Value SUM], TopPProduct)

Var TopNValue1 = CALCULATE('LPO Combined Molecule Mo (2)'[MAT 2023 Value SUM], KEEPFILTERS(TopPProduct))

Var currentPProduct = SELECTEDVALUE(TopProducts[Product])

Return
IF(currentPProduct  = "Other", OtherValue, TopNValue1)

Also, I have to mention I am using a slicer. So, the products that display change according to the category they come under. Now my question is how can I modify this measure to always include a certain products irrespective of them being in the topN.

Here is an example of my table:

[ThxAlot]

An out-of-box pattern to follow.

Showing the top 5 products and Other row - SQLBI

[TheoC]

@ThxAlot SQLBI is very good in all aspects. However, I'm not sure that this meets the needs of what's been asked in the second aspect of @Afthab's request: "Now my question is how can I modify this measure to always include a certain products irrespective of them being in the topN."

[TheoC · Accepted solution]

Hi @Afthab 

Try to adjust the measure so that you include IN into your logic.  It may need a bit of adjustement but give the below ago and make sure to adjust according to your model:

Top N Plus OurProducts Value = 

VAR TopNValue = 5

VAR AlwaysProductsList = { "A", "B" , "C" ...} // Replace with your own products

VAR TopNPlusOurProducts = 

    UNION (
        TOPN ( TopNValue, ALLSELECTED ( TopProducts ) , 'LPO Combined Molecule Mo (2)'[MAT 2023 Value SUM] ) , 
        FILTER ( ALL ( 'ProductTable' ) , 'ProductTable'[ProductColumn] IN AlwaysProductsList)
    )

VAR AllValue = CALCULATE ( 'LPO Combined Molecule Mo (2)'[MAT 2023 Value SUM] , ALLSELECTED ( TopProducts ) )

VAR OtherValue = AllValue - CALCULATE ( 'LPO Combined Molecule Mo (2)'[MAT 2023 Value SUM] , TopNPlusOurProducts )

VAR TopNPlusOurProductsValue = CALCULATE ( 'LPO Combined Molecule Mo (2)'[MAT 2023 Value SUM] , KEEPFILTERS ( TopNPlusOurProducts ) )

VAR CurrentProduct = SELECTEDVALUE ( TopProducts[Product] )

RETURN

IF ( CurrentProduct = "Other" , OtherValue , IF ( CurrentProduct IN OurProductsList , TopNPlusOurProductsValue , TopNPlusOurProductsValue ) )

Hope it helps.

Theo

[Afthab]

Hi @TheoC,

Thank you for the solution. This does indeed return what I want. However, the total shown is not the same as the actual sum total. Please see below screenshot:

First value is total and the second is new measure created

[TheoC]

Hi @Afthab 

Are you able to send through the data you're working with?  The reason I ask is that the $10,066,415 and the $5,891,691 are different to the sum of the example table you provided earlier. 

It's also important to remember the measure focuses on the TopN and also the "always included" products.  I am unsure how the total $10,066,415 is being calculated. 

I would need to get a better understanding of your sample data if possible?

Thanks heaps.

Theo