Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
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.
Hi @Anonymous,
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) ) )
Regards
Hi @v-ljerr-msft,
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.
For ex:
Plant | Customer | Material | Actauls | Forecast | ForecastAccuracy |
1 | 1 | 1 | 100 | 110 | 0.909090909 |
1 | 2 | 1 | 120 | 150 | 0.8 |
2 | 1 | 1 | 50 | 60 | 0.833333333 |
3 | 2 | 1 | 60 | 50 | 1.2 |
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:
Customer | Material | ForecastAccuracy | ||
1 | 1 | 150 | 170 | 0.882352941 |
2 | 1 | 180 | 200 | 0.9 |
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?
Hi @Anonymous,
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.
Regards
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |