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

Hi Anonymous  ,

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.