Forum Discussion

ak23401's avatar
ak23401
Regular Visitor
2 years ago

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:

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

 

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

 

 

 

Need Help

1 Reply

  • Shravan133's avatar
    Shravan133
    Super User
    2 years ago

    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 )