Forum Discussion

tobiasmcbride's avatar
tobiasmcbride
Helper 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:   Forecast Type Q1 Q2 Q3 Q4 FY19 Budget Total $$$ $$ $$$ $ F...
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.

v-lionel-msft's avatar
v-lionel-msft
Community Support
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.