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,
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
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |