Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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

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):

 

ItemVersionAmount EURFXUSD @ ActualsFXUSD@ForecastFXUSD@BudgetFX
SalaryActuals5001,075537,5535550
T&EActuals6001,075645642660
OtherActuals8001,075860856880
SaleryForecast10001,07107510701100
T&EForecast6001,07645642660
OtherForecast8001,07860856880
SaleryBudget4001,1430428440
T&EBudget6001,1645642660
OtherBudget9001,1967,5963990

 

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:

 

 ActualsForecastVarianceActuals@ForecastFXFX VarianceOperational Variance
Salary537,51070-532,55352,5-535
T&E645642364230
Other860856485640
Total2042,52568-525,520339,5-535

 

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

 ActualsBudgetVarianceActual@BudgetFXFX VarianceOperational Variance
Salary537,5428109,5550-12,5122
T&E6456423660-1518
Other860963-103880-20-83
Total2042,520339,52090-47,557

 

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

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.