Calculating variance

[tobiasmcbride]

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	$$$	$$	$$$	$
FY19 Estimate 1	Total	$$	$$	$$$	$
FY19 Estimate 2	Total	$$	$$$	$$$$	$$
FY19 Estimate 3	Total	$$$	$	$$$	$

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.

[v-lionel-msft · Accepted solution]

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.

[amitchandak]

With the help of slicer/s you have control the calculation. Please find example

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...