Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |