cancel
Showing results for
Did you mean:
Frequent Visitor

## Dynamic Column

Hi guys

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.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Dynamic Column

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
2 REPLIES 2
Community Support Team

## Re: Dynamic Column

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Frequent Visitor

## Re: Dynamic Column

Thanks so much Frank!

Announcements