How to show top n + default product in a visual

[msingh2019]

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.

[amitchandak]

@msingh2019 , not very clear , refer if this can help -https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

[Anonymous]

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:

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

[msingh2019]

@Anonymous 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?

[Anonymous]

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

Best regards,

Eyelyn Qin

[msingh2019]

@Anonymous  

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.

[Anonymous]

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

[FarhanAhmed]

Make sure that braces are correct. 

Can you please share the formula that you are writing

[Greg_Deckler]

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

[msingh2019]

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

[Greg_Deckler]

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