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.

       

      https://youtu.be/acjAXy5LNz0

       

        • 1. Re: Variance to Average
          swaroop.gantela

          Andre,

           

          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])

          END

           

          // 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.

           

          283346var.png

          1 of 1 people found this helpful