cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## 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.

3 REPLIES 3
Super Contributor

## Re: Calculation at different granularity: Demand Planning

Hi @atulj10,

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

Regular Visitor

## Re: Calculation at different granularity: Demand Planning

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?

Super Contributor

## Re: Calculation at different granularity: Demand Planning

Hi @atulj10,

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