6 Replies Latest reply on Feb 7, 2018 8:04 AM by Kyle Bogaert

# Dynamic sorting, filters, and top n

Hi all,

I have been digging through other solutions related to dynamic filters to sort stacked bar charts as well as filtering for the top n, but I have not come across a solution that I have been able to adapt and implement.  Attached is a mock workbook that effectively mirrors what I am working with, that includes very limited dummy data.

Dynamic sorting of stacked bar charts

• I have a set of three stacked bar charts. Each portion of the bar reflects a value related to proficiency/importance for a particular skill. The bar charts are filtered on location, and on the variable.
• I want to sort the bar charts from largest to smallest value by a single portion of the bar chart - the orange 'high imp/low skill'.
• I am able to do that for a given location no problem.
• However, when I change the location filter (which applies to the whole workbook, which in reality is about 35 sheets), the dynamic sort is not retained - i.e. changing the location changes the values in the bar chart, but does not dynamically sort so that the bar with the largest orange 'high imp/low skill' value is at the top.
• What modifications do I need to make to retain the dynamic sort upon changing the location filter?

Finding top 'n' for single portion of stacked bar chart

• I also want to be able to limit the information displayed in the bar chart to only show the bars with 3 largest 'high imp/low skill' values.
• I assume I would need to create a parameter for this, but am not sure where to start - or how resolving the dynamic sorting might impact the parameter.

Feedback is appreciated!

• ###### 1. Re: Dynamic sorting, filters, and top n

Hi Kyle - I only worked on your first sheet so you will need to carry over the calculation to the other 2

first I created this brute force measure that sets the target total to the sum of the value and the rest to 0

{ FIXED [Location],[Variable],[Label]: (if min(upper([Label]))

='HIGH IMP/LOW SKILL' then sum(Value) else 0 end) }

then I used the formula in a ranking

On the viz do this

set the table calculation (ranking) as shown

change the sort to

that will give you this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: Dynamic sorting, filters, and top n

Jim - thank you for your assistance - this did exactly what I had hoped it would! I appreciated the clear step-by-step directions provided.

• ###### 3. Re: Dynamic sorting, filters, and top n

this was a cool problem - just had to approach it from a little different view

Jim

• ###### 4. Re: Dynamic sorting, filters, and top n

One follow on though Jim, now that I'm implementing this fix in my actual workbook:

• How does the table calculation handle circumstances where there are no values for one of the 4 categories? For example, if there was a bar that only had values for three of the categories (high imp/high skill, high imp/low skill, and low imp/high skill, but no fourth 'low imp/low skill')?

Right now in my actual data there are some circumstances of that, so the table calc is creating some issues.

• ###### 5. Re: Dynamic sorting, filters, and top n

interesting question

you are still targeting only the one High/low field so how the calculation is looking for that value -  it that the one that is null and what problems is it creating?

• ###### 6. Re: Dynamic sorting, filters, and top n

That is sort of right - I have not been able to recreate the issue with the dummy data, and can't share my actual workbook at this point.

In reality, my workbook has about 20-22 'skills' I am measuring with the same 4 categories. One of my 'levels' tends to have a small n, which means that the stacked bars often only have 1-3 of the imp/skill categories represented. The table calc addresses the sorting of the bars by the appropriate category after adding it to detail, but it's the filtering that is causing the issue.

I'm going to spend some time to see if I can recreate the problem in the mock workbook to repost, but any thoughts on the issue at this point?