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

# Variance to Average

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

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. 1 of 1 people found this helpful