Power Bi: FX Variance - show column depending on slicer selection

[rainer_muc]

Hi,

I have to do a variance analysis between actuals, forecast and budget but also devided into fx variance and operational variance. for this I have the following Power Query (simplified):

Item	Version	Amount EUR	FX	USD @ ActualsFX	USD@ForecastFX	USD@BudgetFX
Salary	Actuals	500	1,075	537,5	535	550
T&E	Actuals	600	1,075	645	642	660
Other	Actuals	800	1,075	860	856	880
Salery	Forecast	1000	1,07	1075	1070	1100
T&E	Forecast	600	1,07	645	642	660
Other	Forecast	800	1,07	860	856	880
Salery	Budget	400	1,1	430	428	440
T&E	Budget	600	1,1	645	642	660
Other	Budget	900	1,1	967,5	963	990

In Power Bi I have a slicer and I can select the Version.

First Example, the selection is "Actuals" and "Forecast" and the PowerBi Report should look like the following:

	Actuals	Forecast	Variance	Actuals@ForecastFX	FX Variance	Operational Variance
Salary	537,5	1070	-532,5	535	2,5	-535
T&E	645	642	3	642	3	0
Other	860	856	4	856	4	0
Total	2042,5	2568	-525,5	2033	9,5	-535

Second Example, the selection is "Actuals" and "Budget" and the PowerBi Report should look like the following:

	Actuals	Budget	Variance	Actual@BudgetFX	FX Variance	Operational Variance
Salary	537,5	428	109,5	550	-12,5	122
T&E	645	642	3	660	-15	18
Other	860	963	-103	880	-20	-83
Total	2042,5	2033	9,5	2090	-47,5	57

That means, dependent on what I select in the slicer the colmuns right next to "Variance" should show either "Actuals@ForecastFX" or "Actuals@BudgetFX". Any plan how I could solve this?

Thanks

[Anonymous]

Hi @rainer_muc  ,

Per my test I could not achieve your issue based on slicer, as a workaround, consider using calculated table as shown below:

VarianceCalculationTable = 
VAR SalaryActuals = CALCULATE(MAX('Table'[USD@ActualsFX]), 'Table'[Item] = "Salary", 'Table'[Version] = "Actuals")
VAR SalaryForecast = CALCULATE(MAX('Table'[USD@ForecastFX]), 'Table'[Item] = "Salary", 'Table'[Version] = "Forecast")
VAR SalaryFX = CALCULATE(MAX('Table'[FX]), 'Table'[Item] = "Salary")

VAR TEActuals = CALCULATE(MAX('Table'[USD@ActualsFX]), 'Table'[Item] = "T&E", 'Table'[Version] = "Actuals")
VAR TEForecast = CALCULATE(MAX('Table'[USD@ForecastFX]), 'Table'[Item] = "T&E", 'Table'[Version] = "Forecast")
VAR TEFX = CALCULATE(MAX('Table'[FX]), 'Table'[Item] = "T&E")

VAR OtherActuals = CALCULATE(MAX('Table'[USD@ActualsFX]), 'Table'[Item] = "Other", 'Table'[Version] = "Actuals")
VAR OtherForecast = CALCULATE(MAX('Table'[USD@ForecastFX]), 'Table'[Item] = "Other", 'Table'[Version] = "Forecast")
VAR OtherFX = CALCULATE(MAX('Table'[FX]), 'Table'[Item] = "Other")

VAR SalaryActualsForecastFX = CALCULATE(MAX('Table'[USD@ForecastFX]), 'Table'[Item] = "Salary", 'Table'[Version] = "Actuals")
VAR TEActualsForecastFX = CALCULATE(MAX('Table'[USD@ForecastFX]), 'Table'[Item] = "T&E", 'Table'[Version] = "Actuals")
VAR OtherActualsForecastFX = CALCULATE(MAX('Table'[USD@ForecastFX]), 'Table'[Item] = "Other", 'Table'[Version] = "Actuals")

RETURN 
    UNION(
        SELECTCOLUMNS(
            ROW(
                "Item", "Salary",
                "Actuals", SalaryActuals,
                "Forecast", SalaryForecast,
                "Variance", SalaryActuals - SalaryForecast,
                "Actuals@ForecastFX", SalaryActualsForecastFX,
                "FX Variance", SalaryActuals - SalaryActualsForecastFX,
                "Operational Variance", (SalaryActuals - SalaryActualsForecastFX) - (SalaryActuals - SalaryForecast)
            ),
            "Item", [Item],
            "Actuals", [Actuals],
            "Forecast", [Forecast],
            "Variance", [Variance],
            "Actuals@ForecastFX", [Actuals@ForecastFX],
            "FX Variance", [FX Variance],
            "Operational Variance", [Operational Variance]
        ),
        
        SELECTCOLUMNS(
            ROW(
                "Item", "T&E",
                "Actuals", TEActuals,
                "Forecast", TEForecast,
                "Variance", TEActuals - TEForecast,
                "Actuals@ForecastFX", TEActualsForecastFX,
                "FX Variance", TEActuals-TEActualsForecastFX ,
                "Operational Variance", (TEActuals-TEActualsForecastFX)-(TEActuals - TEForecast)
            ),
            "Item", [Item],
            "Actuals", [Actuals],
            "Forecast", [Forecast],
            "Variance", [Variance],
            "Actuals@ForecastFX", [Actuals@ForecastFX],
            "FX Variance", [FX Variance],
            "Operational Variance", [Operational Variance]
        ),
        
        SELECTCOLUMNS(
            ROW(
                "Item", "Other",
                "Actuals", OtherActuals,
                "Forecast", OtherForecast,
                "Variance", OtherActuals - OtherForecast,
                "Actuals@ForecastFX", OtherActualsForecastFX,
                "FX Variance", OtherActuals-OtherActualsForecastFX ,
                "Operational Variance", (OtherActuals-OtherActualsForecastFX)-(OtherActuals - OtherForecast)
            ),
            "Item", [Item],
            "Actuals", [Actuals],
            "Forecast", [Forecast],
            "Variance", [Variance],
            "Actuals@ForecastFX", [Actuals@ForecastFX],
            "FX Variance", [FX Variance],
            "Operational Variance", [Operational Variance]
        )
    )

Result:

Use the same logic for the Actual and Budget table.

Best regards,

Joyce

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