4 Replies Latest reply on Jun 4, 2016 10:12 PM by rabeeh.elhussein

    Average Discount per Year ? What am I doing wrong ?!

    rabeeh.elhussein

      Hello,

       

      I am trying to calculate the average discount we are giving a customer per year .. Here's my data

       

      Data.JPG

       

      I created 2 calculated fields with this formula

       

      if ATTR([Year])=2015 then

      1- SUM([Net Price])/SUM([List Price])

      END

       

      but i am getting a null when i am dragging the fields to the columns ..

       

      Please help ..

      Tableau.JPG

        • 1. Re: Average Discount per Year ? What am I doing wrong ?!
          Jonathan Drummey

          Hi,

           

          ATTR() is an aggregation that returns the given value if there is 1 and only 1 value in the current context so what the calculation is doing is saying "if there is only 1 year in the current context then return year, if that year is 2015 then return 1-SUM(Net Price)/SUM(List Price) or else return Null, if there are multiple years then the first evaluation returns a variant of Null and the whole calc returns Null.

           

          Here's the way I'd do it. Build a measure that returns the desired %, like this one (I used your 2015 discount as a basis):

           

          SUM([List Price] - [Net Price])/SUM([List Price])

           

          And then use the Year as a dimension in the view:

           

          Screen Shot 2016-06-04 at 2.29.11 PM.png

           

          This way instead of having to write separate calculations for each year there's one calculation and as you add new years to the data the workbook will automatically update.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: Average Discount per Year ? What am I doing wrong ?!
            rabeeh.elhussein

            Thanks,

             

            It would work of course but i want to add to the view the Total Net Sales (2015& 2016)..

            So the final table for this sample data would look like

             

            Customer               Total Net Sales           Average Discount 2015               Average Discount 2016

            A                                30,089                              0.6650                                        0.6916      

            B                                34,304                              0.5886                                        0.6234

             

            Thanks for any suggestion you can give

            • 3. Re: Average Discount per Year ? What am I doing wrong ?!
              Jonathan Drummey

              Hi,

               

              Something to keep in mind is that Tableau is not a cell-based layout application or a traditional reporting tool so when you're trying to duplicate a tabular report coming out of another application then it can be more work to implement in Tableau. Part of this is that text table views like this aren't that great for answering many business questions, various chart types are better and Tableau is better at building those. In any case, for us answerers on the forums it's better to give us an idea of your ultimate goal because that may rule out some potential approaches.

               

              I set up two ways to get your desired results. One uses three measures, for the 2015 and 2015 averages I used a set of record-level evaluations like IF [Year] = 2015 THEN [Net Price] END to build out the calculations, and then Measure Names/Measure Values to build out the table:

               

              Screen Shot 2016-06-04 at 3.37.16 PM.png

               

              For the second solution I used custom grand totals using the MIN/MAX technique described on http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2. The same Discount Measure that I'd originally suggested is used and Year is a dimension in the view, only this measure is set up to return Null in the Grand Total. Then there's a measure that only returns a Non-Null value for the Net Price in the Grand Total and Null in the detail rows. These are both placed on the Text Shelf and laid out like so:

               

              Screen Shot 2016-06-04 at 3.39.05 PM.png

               

              Then I customized the Grand Total label to say Total Net Sales, and also used a custom number format for the display of the Year dimension so it shows the Discount year:

               

              Screen Shot 2016-06-04 at 3.40.08 PM.png

               

              Though it requires more knowledge of Tableau to set up the resulting view has two advantages: 1) As previously noted it'll automatically update with new years of data, and 2) we get the Grand Total formatting options for the Total Net Sales, for example Tableau's default is to put in the border.

              1 of 1 people found this helpful
              • 4. Re: Average Discount per Year ? What am I doing wrong ?!
                rabeeh.elhussein

                Thank you Jonathan .. Your replies added a lot to my knowledge !