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 ...
Need Help
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