Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
msingh2019
Helper II
Helper II

How to show top n + default product in a visual

My requirement is to show the top 3 products based on their sales but also mandatorily include the client company's product (sometimes with very low sales). So that a competitive analysis can be visualised. I have tried using a rank measure (which I could use on a visual filter with the Top N) but so far I have been unsuccessful in achieving the result that I need. I have also gone through numerous articles and forums that talk about including Others etc but none seem to work in my scenario. 

My data looks like this -

Product | Manufacturer | Sales 

P 1 | M 1 | 100

P2 | M 2 | 120

P3 | M 3 | 80

P4 | M 4 | 50

P5 | M 1 | 150

P 6 | M 2 | 110

 

So, top 3 products would be P5, P2 and P6. However, I also need to include P4 (where manufacturer = "M4") by default within my visual. And the visual should show P5, P2, P6 and P4. 

Any suggestions and ideas will be highly appreciated.

10 REPLIES 10
amitchandak
Super User
Super User

v-eqin-msft
Community Support
Community Support

Hi @msingh2019 ,

According to my understanding, you want to display Product based on top3 Sales and a default Manufacturer, right?

 

You could use the following formula:

RankAndDefault =
IF (
    RANKX (
        ALL ( TopAndDefault ),
        CALCULATE ( MAX ( TopAndDefault[Sales] ) ),
        ,
        DESC
    ) <= 3
        || SELECTEDVALUE ( TopAndDefault[Manufacturer] ) = "M4",
    1,
    0
)

After apply the measure to filter (set as is 1),the visualization looks like this:

8.25.31..png

 

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

 

Best Regards,

Eyelyn Qin

@v-eqin-msft For some reason SelectedValue is not recognized as a function in the measure. Intellisense says SelectedValue is not a function. Can you please suggest any alternatives?

You could try to replace SelectValue() with Max() 

 

Best regards,

Eyelyn Qin

@v-eqin-msft  

This is the DAX for my measure

Products to show = IF(RANKX(ALL('Product Sales'),CALCULATE(MAX('Product Sales'[Sales])),,DESC) <= 3 || SELECTEDVALUE('Product Sales'[manufacturer]) = "XYZ",1,0)
 
The column [manufacturer] is a text column and I get an error if I use MAX obviously. 
 
In the meantime I am preparing a sample PBIX that I can share.

Hi @msingh2019 ,

I used your measure and everything was OK, please upload a sample pbix file.Thank you~ 

If I answered your question, please mark my reply as solution, thank you~

 

Best regards,

Eyelyn Qin

Make sure that braces are correct. 

Can you please share the formula that you are writing







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Greg_Deckler
Super User
Super User

@msingh2019 - Seems like you essentially want a Complex Selector - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

 

In your case, the selector would be either in the Top 3 OR Manufacturer = "M4".


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Thanks for your response. However, in my case the result should not be dependent on any user input. The manufacturer = "M" condition is fixed.

@msingh2019 - OK, the Complex Selector is a concept. Essentially, put whatever logic you want into it, user input or not, and return a 1 or a 0. Filter on that. That's the concept. The example is that that, an example of the concept. The example involves user input. An implementation doesn't have to involve user input.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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