3 Replies Latest reply on Apr 14, 2018 7:19 AM by Simon Runc

    Top 5 Average Sales on a secondary axis line

    Fábio Rocha

      Hello all,

       

      So, I'm working with Tableau for quite a while now, and this week I was asked for a particular design that I can't seem to be able to reproduce.

       

      I need to create a bar chart with the sales for a specific brand for each month. In that same bar chart, I need a line that represents the average sales of the top 5 brands for each month.

       

      I was able to get it done for the average of all brands for each month, that's easy... my question is how do I build that secondary axis to get a top 5 brands by sales within each month?

      I tried using sets but to no avail

       

      For the bars used if [Brand]="X" THEN [Sales] else 0 END - this gets me a bar with only the sales for that brand without filtering anything

      for the secondary axis created a calculated field sum([Sales])/COUNTD([Brand]) - this gets me the average sales for each month but for all brands...

       

      Any ideas?

      Thank you!

        • 1. Re: Top 5 Average Sales on a secondary axis line
          Simon Runc

          hi Fábio,

           

          So assuming I've understood correctly, this is one way to do this.

           

          I've used SuperStore so had to create this LoD to get my average at the level of your example

          [Average Sales Per Category/Month]

          {FIXED [Category], DATETRUNC('month',[Order Date]): SUM([Sales])}

           

          You probably won't need this bit depending on the grain of your data.

           

          Next we can use this to RANK the categories (equivalent to your Brand)

          [Average Sales Per Category/Month RANK]

          RANK(AVG([Average Sales Per Category/Month]))

           

          and then we bring this onto the detail and set up like this (so we get a RANK per Category restarting every month). I've put this on the label so you can see what's going on

           

          finally we use this to only bring back the sales for the Top 5

          [Average Sales Per Category/Month RANK Top 5]

          IF [Average Sales Per Category/Month RANK] <=5 THEN

          SUM([Average Sales Per Category/Month])

          END

           

          we bring this onto the detail shelf and set up the reference line

           

          Here you can see what it's doing

           

          Hope that helps, and if not exactly what you want can be adapted using the same basic logic

          • 2. Re: Top 5 Average Sales on a secondary axis line
            Fábio Rocha

            Hello Simon,

             

            Got to retrieve what I needed from your calcs, I can't have brands showing up, so did some formatting and I think it turned out good

             

             

            Thank you very much!

            • 3. Re: Top 5 Average Sales on a secondary axis line
              Simon Runc

              Very nice Viz!

              Glad you were able to generalise the basic method.