3 Replies Latest reply on Jun 20, 2019 4:04 AM by Shanen Kurniawan

    Volume and Price Calculations and Level of Detail

    Ed Yeldham



      I have managed to create working volume and price variance calculations as shown in the attached workbook with superstore data which replicates my live data.


      My question is that having proven my calcs work at a customer and product level as shown in the "Detail" tab when I then summarise to a higher level of detail e.g segment on the "Summary" tab the split of the variance changes.


      I'm assuming this is to do with level of detail as it is calculating at the data level it sees not the underlying data.


      Whilst it might be mathematically correct it will confuse my end users as they will see one set of numbers then drill to detail which is different.


      How do I resolve and have others had similar experiences and how did they resolve?


      Help appreciated.


        • 1. Re: Volume and Price Calculations and Level of Detail
          Patrick Van Der Hyde

          Hello Ed,


          I am not locating a LOD type calculation in this workbook.  The issue as I understand it relates to the total row for [Sales Volume Variance]  not matching when the view is summarized.  


          You could create a calculation such as - {include [Product Name], [Customer Name]: [Sales Volume Variance]}   to get the correct summarized value (119,347)  and then either design a dashboard with this specific field hovering over the existing value or you could try Customizing Totals to Show Different Values Than Table | Tableau Software


          I hope this helps. 



          1 of 1 people found this helpful
          • 2. Re: Volume and Price Calculations and Level of Detail
            Ed Yeldham

            Thanks I had been using Fixed but the include formula works a treat just how i wanted it to having tested my data in excel to benchmark.  Many thanks


            • 3. Re: Volume and Price Calculations and Level of Detail
              Shanen Kurniawan



              I am currently new to Tableau and am learning how to do PV variance with LOD expressions and came across this thread, however when I opened the workbook and followed the initial explanation^ - I noticed [Sales Volume Variance] in Summary tab (by Segment) and [Sales Volume Variance] in Detail tab have the same values even when I filtered by Segment in the Detail tab - I'm trying to understand where's the difference meant by 'split of variance changes'.


              For instance, under 'Detail' tab when I filtered by 'Consumer' segment only, the bottom line of 215,308 is equal to the one shown under Summary tab.


              Also, when I compared the calculation using Patrick's LOD expression {include [Product Name], [Customer Name]: [Sales Volume Variance]} on Summary tab to test (shown as 'CALC1'), I still can't notice where's the difference between Ed's initial calculation vs the LOD expression.



              Any help on understanding this & how to properly use the LOD expression in this case is much appreciated.


              Thank you