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

New Member

## Dynamic Top N Ranking with Slicers on other columns

I have a table with the following headers:

[Product    Application   Sales    GrossMargin    SellingPrice]

I want to create a dynamic Top N scatter plot with Sales being the size of the bubble, GrossMargin on x axis; and SellingPrice on y axis.

I have a slicer which goes from 1 to 10 (for choosing the value of N in Top N).

I also have a slicer for Product.

Now I have created a Top N scatter chart which shows me top N "Application" on the graph based on the sales value.

But now suppose I have chosen N value to be 10, and I unselect a certain product using the slicer, that products bubbles will dissappear from the graph and say I will be left with 8 bubbles on the graph which will belong to the other products. This is not what I want; I want that since the value of N is 10 and only 8 applications are being displayed on the graph, it automatically chooses the next 2 applications with highest sales of the remaining products to be displayed.

How do I make that happen?

I have used the following measures to make my graph right now:

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

Solution Supplier

try this:

Modify it according to your measures:

### Create a Measure for Top N Filtering

First, you need a measure to dynamically calculate the top N applications based on sales. This measure will be used to filter the scatter plot.

#### 1.1 Create a Measure for Total Sales

Create a measure to calculate total sales:

Total Sales = SUM('YourTable'[Sales])

#### 1.2 Create a Measure for Rank

Create a measure to rank the applications based on sales:

Application Rank = RANKX( ALLSELECTED('YourTable'[Application]), [Total Sales], , DESC, DENSE )

This measure ranks the applications based on the total sales, considering any slicers or filters applied.

#### 1.3 Create a Measure for Top N

Create a measure to filter the top N applications dynamically:

Top N Filter = VAR SelectedN = MAX('TopN Slicer'[Value]) -- Assuming you have a slicer table named 'TopN Slicer' with values from 1 to 10
RETURN IF( [Application Rank] <= SelectedN, 1, 0 )

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.