Filter in chart: Show top N or show items when the value satisfies a condition (>=)

[Anessa]

Please help to instruct on how to set up the line/bar chart with below conditions. Thanks for your support!

X: categories, Y: quantities.

Filter in chart: Show top 5 (X) with highest quantities (Y) OR show items (X) when the value (Y) satisfies a condition (>=). That means it should satify below cases:

• Case 1:
  X with 5 categories or less. Chart shows all categories.
• Case 2:
  X with 6 categories, all Y having quantities < 100. Chart shows the top 5 categories with highest quantities.
• Case 3:
  X with 10 categories, all Y having quantities >= 100. Chart shows all 10 categories.
• Case 4:
  X with 10 categories, 8 categories have quantities >= 100. Chart shows the 8 categories with quantities >=100.

[v-denglli-msft · Accepted solution]

Hi @ Anessa,

Based on what you have described, I have created three tables.

They correspond to 5 categories, 6 categories and 10 categories.

Then create a page for each case and create corresponding bookmarks.

Case 1:

Case2: The top 5 categories are filtered by the filter /Top N/ option.

Case3&4:  Creates a measure MoreThan100 = if(SUM(Table_10_categories[Quantity])>=100,1,0)

                 This measure is then applied to the filter.

                 Set the filter to show items where MoreThan100 is equal to 1

This way, you can view the charts in different cases via bookmarks.

As for your reference to setting up 1 chart to satisfy all these cases.

This is not possible to achieve.

Best Regards,

Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@Anessa , For

• Case 1:
  X with 5 categories or less. Chart shows all categories. :- No additional filtering is needed. The chart will automatically display all categories.
• Case 2:
  X with 6 categories, all Y having quantities < 100. Chart shows the top 5 categories with highest quantities.:- Create a measure 
• RankCategories = RANKX(ALL('Table'[Category]), CALCULATE(SUM('Table'[Quantity])), , DESC, Dense)
• Add a visual-level filter to show only the top 5 categories:
  Drag the RankCategories measure to the Filters pane.
  Set the filter to show items where RankCategories is less than or equal to 5.
• Case 3:
  X with 10 categories, all Y having quantities >= 100. Chart shows all 10 categories.
• Create a measure FilteredCategories = IF(SUM('Table'[Quantity]) >= 100, 1, 0)
• Add a visual-level filter to show only categories where FilteredCategories is 1:
  Drag the FilteredCategories measure to the Filters pane.
  Set the filter to show items where FilteredCategories is equal to 1.
• Case 4:
  X with 10 categories, 8 categories have quantities >= 100. Chart shows the 8 categories with quantities >=100.
• Use the same FilteredCategories measure from Case 3.
  Add a visual-level filter to show only categories where FilteredCategories is 1:
  Drag the FilteredCategories measure to the Filters pane.
  Set the filter to show items where FilteredCategories is equal to 1.

[Anessa]

@bhanu_gautam thanks for your quick response, actually what I need is how to set up 1 chart to: show top 5 (X) with highest quantities (Y) OR show items (X) when the value (Y) satisfies a condition. The cases are the demonstrations of how that 1 chart should function. Your reply divides it by cases (eventually 4 charts) so it misses the point. Do you have any way to set up 1 chart satisfying all those cases?