2 Replies Latest reply on Sep 6, 2018 6:45 AM by Roberto Nieto

    Running Total Average and LOD

    Roberto Nieto

      Hi everybody

       

      I am trying to get a Running Total Average of Sales per day using the following dataset:

       

       

      DateOrder IDSales
      01/01/2018A00011
      01/01/2018A00025
      01/01/2018A00033
      01/01/2018A00044
      02/01/2018A00053
      02/01/2018A00061
      02/01/2018A00071
      02/01/2018A00080
      02/01/2018A000925
      02/01/2018A0010200
      02/01/2018A0011250
      03/01/2018A00123
      03/01/2018A00136
      03/01/2018A00141
      03/01/2018A0015

      1

      Average33.6

       

      This is what I would like to achieve:

       

          

      DateOrder IDSalesRunning Total SumRunning Sum of RecordsRunning Avg (LOD OrderID)
      01/01/2018A00011111.00
      01/01/2018A00025623.00
      01/01/2018A00033933.00
      01/01/2018A000441343.25
      02/01/2018A000531653.20
      02/01/2018A000611762.83
      02/01/2018A000711872.57
      02/01/2018A000801882.25
      02/01/2018A0009254394.78
      02/01/2018A00102002431024.30
      02/01/2018A00112504931144.82
      03/01/2018A001234961241.33
      03/01/2018A001365021338.62
      03/01/2018A001415031435.93
      03/01/2018A001515041533.60

       

       

       

      However as I don't include OrderID on the pill's mark  Tableau calculates average based the aggregation by date instead of the lowest level of detail:

       

          

      DateAverage per dayRunning Sum of RecordsRunning Total SumRunning Avg (LOD Date)
      01/01/20183.2513.253.25
      02/01/201868.57271.8235.91
      03/01/20183374.82

      24.94

       

       

      I tried the following calculated field however it does not seem to work:

       

      { INCLUDE [Order ID]: AVG([Sales])}

       

       

      I attached example file.

       

      Many thanks

       

      Roberto