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

    Dynamic sorting, filters, and top n

    Kyle Bogaert

      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
          Jim Dehner

          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

           

           

          then add the filter

           

           

          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
            Kyle Bogaert

            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
              Jim Dehner

              Glad to help out

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

              Jim

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

                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
                  Jim Dehner

                  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
                    Kyle Bogaert

                    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?