1 Reply Latest reply on Sep 28, 2018 6:22 PM by swaroop.gantela

    Variance to Average

    andre riley

      Looking to understand how I should calculate variance to average using Superstore. Essentially what I want to achieve is an average sales total for the four regions in the total rows and then the values for each region should be a variance to the average stated in the total row. I believe this will require use of the SIZE() function detailed in the youtube link below but not sure exactly how to set this up.




        • 1. Re: Variance to Average



          I didn't fully catch the requirements (and couldn't open the video),

          but just wanted to toss out some ideas, in case they may be pertinent.


          I think a combination of Level of Detail calculations

          Overview: Level of Detail Expressions

          and a Table calculation may get you there.


          The [Avg Sales Per Month] would be:


          { FIXED [Order Date (Months)]:SUM([Sales])}


          { FIXED [Order Date (Months)]:COUNTD([Region])}


          The variance would then be:

          { FIXED [Region],[Order Date (Months)]:SUM([Sales])}

          -[Avg Sales Per Month]


          To display the variance in the table, and then avg in the total

          you could try this trick:

          IF WINDOW_MIN(MIN([Region]))=WINDOW_MAX(MAX([Region]))

          THEN SUM([Variance])

          ELSE SUM([Avg Sales Per Month])



          // for the total row, the max region doesn't equal the min region)

          // "Compute Using" of "Cell"


          Please see the workbook v10.3 attached in the Forum Thread.



          1 of 1 people found this helpful