2 Replies Latest reply on Aug 7, 2018 8:40 AM by Amit Sharma

    Sorting based on grand total

    Amit Sharma

      Hello,

       

      I have 2 columns; True and False. I am trying to sort based on 'percentage total' for True. But I also want to take into consideration the grand total (True + False) which needs to be at least a minimum number (20 for example below). Anything below the minimum grand total needs to be filtered from the worksheet. Sorting has to be descending order, i.e., highest percent total of False to lowest percent of False.

       

      Before

       

      PresentFalseTrue
      Grand Total
      Data 13 (37.5%)5 (65.5%)8 (100%)
      Data 211 (34.38%)21 (65.62%)32 (100%)
      Data 317 (85%)3 (15%)20 (100%)
      Data 49 (40.91%)13 (59.09%)22 (100%)
      Data 521 (72.41%)8 (27.59%)

      29 (100%)

      Data 615 (78.95%)4 (21.05%)19 (100%)
      Data 718 (60%)12 (40%)30 (100%)
      Data 820 (68.97%)9 (31.03%)29 (100%)
      Data 97 (26.92%)19 (73.08%)26 (100%)
      Data 104 (33.33%)8 (66.67%)12 (100%)

       

       

       

      After

       

      PresentFalseTrue
      Grand Total
      Data 317 (85%)3 (15%)20 (100%)
      Data 521 (72.41%)8 (27.59%)29 (100%)
      Data 820 (68.97%)9 (31.03%)29 (100%)
      Data 718 (60%)12 (40%)30 (100%)
      Data 49 (40.91%)13 (59.09%)22 (100%)
      Data 211 (34.38%)21 (65.62%)32 (100%)
      Data 97 (26.92%)19 (73.08%)

      26 (100%)

       

       

      Thanks in advance.

        • 1. Re: Sorting based on grand total
          Steve Martin

          Hi Amit,

           

          You can achieve the sort by converting your perc total calculation to discrete and placing it wherever needed on the rows (or columns depending on your output).

           

          In the attached, I have three sheets

          • Unsorted which demonstrates the unsorted list
          • Window sorted which demonstrates based on the entire window
          • pane sorted which demonstrates the sort per pane

           

          The calc is simple: Sum(Sales) / Window_Sum(Sum(Sales)) << note the use of window_sum here instead of Total - these are table calcs which are aggregate; choosing window_sum over Total() as it offers more flexibility.

           

          Steve

          • 2. Re: Sorting based on grand total
            Amit Sharma

            Hello Steve,

             

            I don't see any attached sheets with your response.

            But anyways, what I did was I created a calculated field of Grand Total and used it as a filter. And then converted the True and False column as perc total and then sorted them and that worked. I would still like to see your option and try. Thank you for your input though.


            Best,
            Amit