cancel
Showing results for
Did you mean:

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

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
Super User
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:

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

Best Regards,

Eyelyn Qin

Helper II

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

Community Support

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

Best regards,

Eyelyn Qin

Helper II

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.
Community Support

Hi @msingh2019 ,

Best regards,

Eyelyn Qin

Community Champion

Make sure that braces are correct.

Can you please share the formula that you are writing

Proud to be a 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".

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors