2 Replies Latest reply on Oct 2, 2017 3:06 PM by ann.stolzman

    Dividing a dataset into 4 categories that always appear

    ann.stolzman

      Hi all,

      I have a new quandry.  Attached is a workbook that mimics something we're doing in-house, and I need some help.  I need to assign a category to a dimension (that you can select from a parameter) based on the value.  I've got that part down.  The problem is this:

       

      Instead of assigning the Tiers based on the ratio % that is calculated, I want to break the set up evenly into 4 so that Tier 1 is always the top 25% of the set, Tier 4 is always the bottom 25% of the set, and then you have Tier 2 & 3 with their relative 25% of the population.  Then, I need to create a sheet where all 4 of those categories appear without other dimensions or data elements appearing in the sheet.  This is important, because we're using those categories as a visual filter (because of the way we've designed something, and we're locked into that design now). 

       

      I asked about something else recently related to this, because we had categories that didn't appear due to a data model change, and thus, one of our tiers is blank (I asked about that here: How to show an empty set )

       

      Is there a way, based on what I have in this workbook, to divide the dataset as described above?

       

      Thanks in advance!

      -A-

        • 1. Re: Dividing a dataset into 4 categories that always appear
          Justin Larson

          Great workbook, and good problem.

           

          If you turn your Weighted Ratio into a FIXED LOD calculation, then it can be treated as a row-level calculation.

           

          [Weighted Ratio per Dim]:

          { FIXED [Selected Dimension]:SUM([Quantity]*[Selected Measure])/SUM([Selected Measure])}

           

          Then you can do your Rank Category calculation based on that as a row calculation as well, rather than a window calculation that relies on the LOD being on the canvas:

          if [Weighted Ratio per Dim] > { FIXED [Select Dimension]: PERCENTILE([Weighted Ratio per Dim],.75)} then 'Tier 1'

          elseif [Weighted Ratio per Dim] > { FIXED [Select Dimension]: PERCENTILE([Weighted Ratio per Dim],.5)} then 'Tier 2'

          elseif [Weighted Ratio per Dim] > { FIXED [Select Dimension]: PERCENTILE([Weighted Ratio per Dim],.25)} then 'Tier 3'

          else 'Tier 4'

          end

           

          This allows you to mark each row's Tier, but based on the aggregate across rows. You may have to alter the way you were using WeightedRatio - such as taking an average of it, rather than a sum, since now it's returning values per [Selected Dimension], then doing the selected aggregate.

          • 2. Re: Dividing a dataset into 4 categories that always appear
            ann.stolzman

            Actually, it seems to work correctly in the model that I have with the real data.  I knew it had to be something simple - I should have started with the LOD in the first place.  I was approaching the LOD idea from the last mile of the problem instead.

             

            Thanks for the solution, and for the compliment on the workbook.   

             

            -Ann-