Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
With the help of slicer/s you have control the calculation. Please find example
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |