cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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:

1 ACCEPTED SOLUTION
Super User

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

5 REPLIES 5
Super User

An out-of-box pattern to follow.

Showing the top 5 products and Other row - SQLBI

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Super User

@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."

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Super User

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Frequent Visitor

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

Super User

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!