8 Replies Latest reply on Apr 11, 2017 12:43 AM by Sagar Agarwal

    Median of Average Sales and Average of Average Sales

    Sagar Agarwal

      Hello All,

       

      I need some help to figure out how do I calculate the Median and Average of my dataset. A challenge I am facing is that I need to calculate these for aggregated measures, which I am unable to do so.

       

      Below is the snapshot of the data. I want to calculate columns G, H and I.

       

      Let me know if I am not clear on the question or if you need additional details. I need to get this thing in a bullet chart in Tableau, so not sure how do I approach it.

        • 1. Re: Median of Average Sales and Average of Average Sales
          Andrew Watson

          It's possible. Based on having 3 fields, Client, Sales 2015 and Sales 2016:

           

          Total Sales (Col F): SUM([Sales 2015]) + SUM([Sales 2016])

           

          AvgPerClient (Col G): [TotalSales]/2

           

          Median of Avg Sales (Col H): WINDOW_MEDIAN([AvgPerClient])

           

          Avg of Avg (Col I): WINDOW_AVG([AvgPerClient])

           

          Result is:

          1 of 1 people found this helpful
          • 2. Re: Median of Average Sales and Average of Average Sales
            Travis Stone

            Hey Sagar,

             

            I would start off with some LOD expressions, you can find some good examples here . (Your example might best fit number 7 or 8).

             

            First, I would arrange your data so that all [Sales] are in one column, and [Sale Date] is another column, this will make it easier to analyze in Tableau.

             

            Secondly,

             

            Total Sales per Client

                 { FIXED [Client] : SUM(Sales) }

             

            This calculation would find the total sales for each client for all years (if you re-arrange your data like noted above).

            Then you can find the average sales over all years by dividing this number by the dividing it by the COUNTD(YEAR(Date)).

             

            Average Yearly Sales per Client:

                SUM( [Total Sales per Client])  / COUNTD(YEAR([Date]))

             

            The median would be more difficult, but could be done with a Table Calculation. I don't believe a regular WINDOW_MEDIAN would work, so you would most likely need to do a combination of INDEX(), sorted by Average Yearly Sales, and then divide by two to find the Median.

             

            The average of averages should be able to be done using a WINDOW_AVERAGE function however.

             

            If you need more specifics on these you can send me a sample dataset and I will show you what I mean in a workbook, but hopefully this gets you started on the right foot!

            -Travis

            1 of 1 people found this helpful
            • 3. Re: Median of Average Sales and Average of Average Sales
              Sagar Agarwal

              Hi Andrew,

               

              Thank you very much, this worked for the worksheet where I report raw numbers. I am, however, unable to put this on a bullet/bar chart as a fixed line because I am running into a restriction. Let me explain:

               

              Refer to the below screenshot: I want to report the sales for each category, but I also want to display a median of sales by taking the sales of all customers and taking its median. This very well comes in when I use the customer name pill on the sheet, but I am just unable to do so when the customer name is not present on the viz. I hope I am clear.

               

              I want to show the median line of 749 for Furniture, 552 for Office Supplies, and 590 for Technology. This has to be a fixed line irrespective of what filters I apply.

               

               

              • 4. Re: Median of Average Sales and Average of Average Sales
                Sagar Agarwal

                Hi Simon Runc - would you be please able to help me in this? I have to publish this dashboard today EOD.

                • 5. Re: Median of Average Sales and Average of Average Sales
                  Simon Runc

                  hi Sagar,

                   

                  So just to let you know all the contributors on the forum are volunteers, giving our time for free. As such we have no SLA/deadlines, and you shouldn't plan your work deadlines on the basis of this resource (eg. EoP today!). That said, it's not a totally selfless pursuit, as we all learn a lot from each other & try to be as accommodating/helpful as possible.

                   

                  So onto the problem...so we can (as several others have alluded to) solve this with LoDs. As LoDs are calculated before any regular filters are applied when we create this as an LoD is won't get affected by regular filters.

                   

                  So the formula is

                  [Median - LoD]

                  {FIXED [Category]: MEDIAN({FIXED [Customer Name], [Category]:  SUM([Sales])})}

                   

                  I'm taking the SUM of Sales by Customer and Category...and then taking the MEDIAN of this, FIXED against Category.

                   

                  I can then bring this into the detail pane, so I have access to it in the Reference Lines...and set up the reference line as follows (Note the Per Cell)

                   

                   

                  hope that makes sense.

                  1 of 1 people found this helpful
                  • 6. Re: Median of Average Sales and Average of Average Sales
                    Sagar Agarwal

                    Hello Simon,

                     

                    So just to let you know all the contributors on the forum are volunteers, giving our time for free. As such we have no SLA/deadlines, and you shouldn't plan your work deadlines on the basis of this resource (eg. EoP today!). That said, it's not a totally selfless pursuit, as we all learn a lot from each other & try to be as accommodating/helpful as possible.

                    I truly apologize for this; I had this thing in mind before I tagged you. But to be very honest, I had myself given it good 3 days before I even started out this thread on Tuesday and hence this. But I will surely keep this in mind going forward

                     

                    Also, thank you very much for your solution, it helps me fix the problem I was facing. I have a small question with respect to LODs though - am I right in assuming that they are most of times always used for such aggregations? I take it to be the equivalent of the [group by + some mathematical operation] clause that I used in traditional SQL. Is it that way always?

                     

                    Thank you.

                    • 7. Re: Median of Average Sales and Average of Average Sales
                      Simon Runc

                      Cool...glad it did the trick!

                       

                      So that's a big question!...

                       

                      Whenever you add a regular dimension to the Viz (Columns, Rows, Detail, Colour...etc.) that is akin to a GROUP_BY (Tableau, in short, works like a database, where the queries are generated by drag and drop, rather than code, and the results are returned in "render instructions" rather than tables). So if we have SUM([Sales]) from Superstore, and add in Region to the column shelf, say, this is the same as

                       

                      SELECT [Region], SUM([Sales]) GROUP_BY [Region]

                       

                      in fact if you connect live to a SQL datasource and use the Performance Recorder (which captures the queries sent) you'll see this is exactly what it does. This is known as the VizLoD (the Canvas/Viz Level of Detail)

                       

                      This is great, but there is a problem (where your situation, is a great example) where the Calculation Detail is (or CalcLoD) is different to the Viz (or Display) LoD (you want to display by Category, but you want the measure calculated at the customer Level). This is what FIXED LoD calculation are (generally) for. Lets look at an example where were want to show the Region in the Viz, but we want the Average Sales of Each State as our value (but we don't want to have State in the VizLoD). For simplify let's also assume our DataLoD (grain of the actual data) is just at City Level....Notice we have 3 LoDs....The Data LoD, the Calculation LoD and the Viz LoD.

                       

                      So our formula is

                      {FIXED [State]: AVG([Sales])}

                       

                      In simple terms what it does is...sends off a sub-query in a "temp" table to create this FIXED Aggregated Table...

                       

                      SELECT [State], AVG([Sales]) GROUP_BY [State]

                       

                      it then (conceptually) left joins this temp table back to the Original Data using [State] as the Join field.

                       

                      As we have FIXED the level at which this is calculated, the calculation is an "Off-Canvas" one (as I refer to them), meaning it doesn't matter what the VizLoD is...which is why a return from an LoD can sit in either the Dimension pane, or measure pane. In our first example the SUM of Sales depends on the VizLoD (so if we brought in State rather than Region the SUM of Sales is different...as an Aggregate calculation the result is determined by the VizLoD...so the calculation is "on canvas" and can only sit in the measures pane)

                       

                      Hope that makes sense?...I think you might also find this interesting/useful Answer - Quora (describing [conceptually] what the calculations types, in Tableau, do and are)

                      1 of 1 people found this helpful
                      • 8. Re: Median of Average Sales and Average of Average Sales
                        Sagar Agarwal

                        Your Quora answer is Gold! Thank you very much for it