Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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
Thanks so much Frank!
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |