Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Afthab
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:

Afthab_0-1699814176827.png

 

 

1 ACCEPTED SOLUTION
TheoC
Super User
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!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

5 REPLIES 5
ThxAlot
Super User
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)


)



@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!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
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!

Want to connect?www.linkedin.com/in/theoconias

Afthab
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:

Afthab_0-1699866909909.png

 

First value is total and the second is new measure created

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!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.