In my Demand planning forecast report, I need to calculate my Actuals and Forecasts at different grains.
I have Plant, Customer, Material for which I calculate my Actuals and Forecast. I am trying to calculate them at different levels like
FORECAST(plant-material), FORECAST(plant-customer-material), FORECAST(Customer-material).
I believe I could use a 'If' condition to act as a 'Case' and do the Calculation, but I am not sure how it would actually work?Something like this below?
Have a slicer as Data level:
Data Level: Plant-Customer-Material is "1"
Plant-Material is "2"
Customer-Material is "3"
If 1 then calculate Forecast(Plant_Cust_Mat)
If 2 then calculate Forecast(Pant_Mat)
If 3 then calculate Forecast(Cust-Mat)
I just don't understand how I can achieve this.
If I understand you correctly, the formula(DAX) should be similar like below.
Measure = IF ( HASONEVALUE ( [Data Level] ), SWITCH ( VALUES ( [Data Level] ), 1, Forecast(Plant_Cust_Mat), 2, Forecast(Pant_Mat), 3, Forecast(Cust-Mat) ) )
Thank you for the response. And yes, you understood it correctly. That is what I am trying to achieve, but the hard part I don't understand is calculating the ForacastAccuracy at different levels.
If I want my ForecastAccuracy formula to execute at plant-customer-material, I will get the above results
Next, at Customer-Material level, I should get:
So, by selecting the slicers from the parametter table(Data level), I can have my chart display ForecastAccuracy for different granularity.
I don't understand how to write the dax for these different level of data. Does this make sense?
As there is no any option to dynamically show/hide columns on Table/Matrix based on Slicers currently, I don't think there is way to do it in your scenario.