Dynamic Column

[lippert]

Hi guys

Thanks in advance for your help.

I need help creating a dynamic column, that switches between Actuals 2018 and Forecasts 2019.

The table currently looks like the below, with forecasts 2019 being the dynamic column:

The user has to have the option to toggle between actuals 2018 or forecasts 2019. Perhaps a parameter? The option to toggle between these two fields has to appear as a slicer on the report ideally.

When choosing actuals 2018 or forecasts 2019 for the dynamic column, the actual 2019 column would not be affected, but the variance and variance % would change. The calculation for variance = (Actual 2019 - (Actual 2018 or Forecast 2019 - dynamic column). Variance % = Variance / Dynamic column

I hope this is clear.

Please ask if there is anything that seems unclear.

Thanks!

[v-frfei-msft · Accepted solution]

Hi @lippert,

I made one sample for your reference. Although we cannot achieve the exactly same goal as you want. We can can do the steps as below to work around.

1. Enter a table directly as a slicer table.

2. Create measures as below.

Variance = var _sele = SELECTEDVALUE('Slicer'[slicer])
return
IF(_sele= "Actual 2018",SUM(Table1[Actual 2019])-SUM(Table1[Actual 2018]),SUM(Table1[Actual 2019])-SUM(Table1[Forecast 2019]))

%Variance = var result = [Variance]/SUM(Table1[Forecast 2019])
return
IF(ISBLANK(SUM(Table1[Forecast 2019])),BLANK(),result)

Please check the pbix as attached.

Regards,

Frank

[lippert]

Thanks so much Frank!