Changing by value in top N filtering

HI MattTaylor280 ,

You can create a dynamic "Top N" filter in Power BI that allows users to change the field by which the table is filtered. Here's how you can achieve this:

• Create a Parameter for the Top N Value:
  • Go to the Modeling tab and select New Parameter.
  • Set up a parameter (e.g., "Top N") with a range of values (e.g., 1 to 20).
  • This parameter will let users choose how many items they want to see in the table (e.g., Top 10, Top 5, etc.).
• Create a Slicer for the Measure Selection:
  • Create a new table (can be done manually in Power BI) with the names of the measures you want to use as the criteria for filtering.                                                                                                                                      For example:Measure Selector =
    DATATABLE(
    "Measure", STRING,
    {
    {"Price"},
    {"Quantity"},
    {"Discount"}
    })
  • This table will be used as a slicer for users to choose the field they want to use for the "Top N" filter.

• Create a Dynamic Measure:

  • Use a DAX formula to create a dynamic measure based on the selected value in the slicer. For example:Selected Measure Value =
    SWITCH(
    SELECTEDVALUE('Measure Selector'[Measure]),
    "Price", SUM('YourTable'[Price]),
    "Quantity", SUM('YourTable'[Quantity]),
    "Discount", SUM('YourTable'[Discount]),
    0)
  • This measure will dynamically calculate the value based on the user's selection.

• Apply the Top N Filter:

   

  • Add the dynamic measure you created as a "Top N" filter in your table visual.

  • Set the Show items option to "Top N" and use the parameter you created in Step 1 for the number of items.

  • For the "By Value" field, use the dynamic measure.

• Set Up the Slicers:
  • Add the slicer for the measure selection (from the table you created in Step 2) to your report.
  • Add the parameter slicer for the “Top N” value.

Now, when the user selects a measure from the slicer, the table will update dynamically to show the top N policies based on the chosen measure (e.g., Price, Quantity, or Discount).

If I have resolved your question, please consider marking my post as a solution🎉. Thank you!