Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Calculation at different granularity: Demand Planning

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.

 

Need Help

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    If I understand you correctly, the formula(DAX) should be similar like below. :smileyhappy:

    Measure =
IF (
    HASONEVALUE ( [Data Level] ),
    SWITCH (
        VALUES ( [Data Level] ),
        1, Forecast(Plant_Cust_Mat),
        2, Forecast(Pant_Mat),
        3, Forecast(Cust-Mat)
    )
)

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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: 

      PlantCustomerMaterialActaulsForecastForecastAccuracy
      1111001100.909090909
      1211201500.8
      21150600.833333333
      32160501.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
      111501700.882352941
      211802000.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?

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        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. :smileyhappy:

         

        Regards