3 Replies Latest reply on Nov 7, 2017 6:36 AM by Jason Rochin

    Adding a column of averages and sorting shapes

    Mattias Elm
    Mattias Elm Nov 1, 2017 7:19 AM

      Hi!

       

      I am trying to work out how to add a column with averages to a view of shapes, and preferrably sorting data on this column.

       

      I have this workbook:
      Tableau Public

       

       

      The stars correspond to a rating; 3 stars good, 1 star bad. The first column is a geographical area, and the second column contains company names.

       

      With this view I would like to do two things:

       

      1, Add a column to this table which corresponds to an average rating for each row. That is, for the first row visible (Avesta/AB Alphace) the average column should list 1,8 ((1+2+2+2+2)/5).

      2, I would then like to sort on this new average column, within the boundaries of the geographical field (eg. "Avesta"). I would like the company (which is the data in the second column) to be sorted from highest average rating to lowest, within the boundaries of the geographical column on the far left ("Avesta").

       

      I have a related question. Is it possible to sort shapes? How would I sort from highest to lowest rating on the last column (sep 2017) within the geographical boundary?

       

      Greatful for any help!

       

      Best regards,

      Mattias

      • 123 Views
      • Categories: Calculations, Formatting & Display, Tableau Public
      • Tags:
        • 1. Re: Adding a column of averages and sorting shapes
          Jason Rochin
          Jason Rochin Employee Nov 6, 2017 9:06 AM (in response to Mattias Elm)

          Hi Mattias,

           

          I can calculate the average per row but could not get a sort to work.

           

          To calculate the average per row use this calculation:

           

          WINDOW_SUM(SUM([Rating])/

          TOTAL(COUNT([Rating])))

           

          This cannot be used as a sort as it uses Table Calculations.

          This is displayed as a discrete value in the table.

          See attached for my attempt.

           

          Thanks,

          Jason R.

          1 of 1 people found this helpful
          • 2. Re: Adding a column of averages and sorting shapes
            Mattias Elm
            Mattias Elm Nov 6, 2017 2:22 PM (in response to Jason Rochin)

            Thanks Jason! Thats a start!

             

            Instead of Nulls I now got #Missing in the table. Can't seem to find that in your code. Any ideas where that came from? An effect of the table calculation. Not that it's a problem, just curious!

             

            Now what remains is figuring out two things:

             

            1, How to sort the table on the average rating column, within the boundary (pane?) of the geographical variable.

            2, Another option that I also need; how to sort on the column for the latest data (sep 2017) from most stars to fewest (again within the boundary of the geographical pane).

             

            Figuring out how to sort shapes isn't easy.

             

            Thanks,

             

            Mattias E.

            • 3. Re: Adding a column of averages and sorting shapes
              Jason Rochin
              Jason Rochin Employee Nov 7, 2017 6:36 AM (in response to Mattias Elm)

              Hi Mattias,

               

              I got rid of the nulls by making the calculation with LOD instead of Table Calcs.

              Still not able to sort though. I think this is because of the way the measure is show as discrete.

               

              'Average Calculation'

              (SUM([Rating])) / SUM({ FIXED  [Leveransområde],[Leverantör (group) 1]:COUNT([Rating])})

               

              'LOD Average of Total/Count' - This one goes on rows as discrete

              AVG({ FIXED [Leveransområde],[Leverantör (group) 1]:([Total Sum Ratings]/[Count Number or Ratings])})

               

              Thanks,

              Jason R.

              1 of 1 people found this helpful