Forum Discussion

tobiasmcbride's avatar
tobiasmcbride
Icon for Helper III rankHelper III
6 years ago
Solved

Calculating variance

Hi,

 

I have some revenue data and am struggling to calculate the variance of this. The data is formatted as below:

 

ForecastTypeQ1Q2Q3Q4
FY19 BudgetTotal$$$$$$$$$
FY19 Estimate 1Total$$$$$$$$
FY19 Estimate 2Total$$$$$$$$$$$
FY19 Estimate 3Total$$$$$$$$

 

I want to be able to calculate the variance between any of the values in that table across the quarters against the FY19 Budget (i.e. be able to visualise and then conditionally format the variance between FY19 Estimate 1 and Budget, Estimate 2 and Budget and Estimate 3 and Budget (But also have ability to change it to be FY19 Estimate 3 against Estimate 1).

 

What is the best way to do this? Any help much appreciated.

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi tobiasmcbride ,

     

    First, add a [index] column in Edit Queries;

    Second, create a measures for Q1, Q2, Q3:

    Measure for Q1, Other similar:

    variance _Q1 = 
VAR x =
CALCULATE(
    MAX(Sheet2[Q1]),
    FILTER(
        Sheet2,
        Sheet2[Index] = MIN(Sheet2[Index])
    )
)
VAR y = 
CALCULATE(
    MAX(Sheet2[Q1]),
    FILTER(
        Sheet2,
        Sheet2[Index] = MAX(Sheet2[Index])
    )
)
RETURN
DIVIDE(
    x-y,
    y
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies