Forum Discussion
Helper VConditional Slicers values for different years
Hi,
I have a peculiar problem. Heres a simple stripped down dataset
The green columns are my input.
Forecast(Red) is what I want as a slicer at an yearly level so that the user can select one forecast value for 1 year.
I want ideas around how should i have my Forecast slicer.
Should the forecast value come from a single column (looks difficult since it would have a different value for different years) ?
I cant afford to have 10 forecast value slicers for each year as in the actual dataset I have 10 years of data along with 5 such parameters including forecast which means I'll need to bring 50 slicers on the table.
Based on the forecast value, The New Value(Red) is calculated as Net Profit * (Forecast/100) + Net Value (that I can handle if the above is figured out)
| Date | Gross Profit | Net Profit | Forecast | New Value |
| 1/1/2020 | 11 | 5 | 5% | 5.25 |
| 2/1/2020 | 42 | 10 | 5% | 10.5 |
| 3/1/2020 | 77 | 1 | 5% | 1.05 |
| 4/1/2020 | 77 | 10 | 5% | 10.5 |
| 5/1/2020 | 97 | 10 | 5% | 10.5 |
| 6/1/2020 | 69 | 7 | 5% | 7.35 |
| 7/1/2020 | 16 | 3 | 5% | 3.15 |
| 8/1/2020 | 50 | 2 | 5% | 2.1 |
| 9/1/2020 | 67 | 2 | 5% | 2.1 |
| 10/1/2020 | 15 | 6 | 5% | 6.3 |
| 11/1/2020 | 23 | 6 | 5% | 6.3 |
| 12/1/2020 | 90 | 5 | 5% | 5.25 |
| 1/1/2021 | 96 | 2 | 6% | 2.12 |
| 2/1/2021 | 24 | 4 | 6% | 4.24 |
| 3/1/2021 | 80 | 8 | 6% | 8.48 |
| 4/1/2021 | 53 | 7 | 6% | 7.42 |
| 5/1/2021 | 95 | 4 | 6% | 4.24 |
| 6/1/2021 | 46 | 7 | 6% | 7.42 |
| 7/1/2021 | 94 | 4 | 6% | 4.24 |
| 8/1/2021 | 64 | 8 | 6% | 8.48 |
| 9/1/2021 | 75 | 10 | 6% | 10.6 |
| 10/1/2021 | 85 | 4 | 6% | 4.24 |
| 11/1/2021 | 95 | 7 | 6% | 7.42 |
| 12/1/2021 | 61 | 7 | 6% | 7.42 |
Finally, I want to add the New value on this matrix just below the Net Profit.
Hi klehar ,
As far as I know, if you want the forecast value come from a single column, you have better to create multiple what-if parameters for each year such as 10 parameters for 10 years and use switch() statement to do the calculations.
About what-if parameters, please refer: Create and use what-if parameters to visualize variables in Power BI Desktop
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
v-yingjlCommunity Support
Hi klehar ,
As far as I know, if you want the forecast value come from a single column, you have better to create multiple what-if parameters for each year such as 10 parameters for 10 years and use switch() statement to do the calculations.
About what-if parameters, please refer: Create and use what-if parameters to visualize variables in Power BI Desktop
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.