Hierarchical Time Series Forecasting in Power BI

[Anonymous]

Hi,

I am currently creating a dashboard for a large company. I have annual data for their supply chain, which contains many facilities. These facilities are owned by supply organistions, which sell to the large, overarching company. I am generating forecasts for each facility and wondered if there is a way to aggregate forecasts to generate forecasts at higher levels. This is known as the bottom-up hierarchical time series forecast approach. 

For example, I have one forecast for Facility A and one forecast for Facility B. I wish to aggregate them to get a forecast for Organisation X, who owns both Facility A and Facility B by aggregating the indiviudal forecasts. 

This is a great simplification of the hierarchy. In reality, the hierarchy is much larger. I would greatly appreciate any guidance on whether this is possible and if so, how to conduct this in Power BI.

Thank you in advance!!

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I assume that you want to calculate the average as the forecast for each facility and then aggregate them to generate a forecast for the parent organisation. I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

ForecastResult = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Organisation],
    "Forecast",
    var _organisation = [Organisation]
    var t=
    SUMMARIZE(
        ALL('Table'),
        'Table'[Organisation],
        'Table'[Name],
        "Avg",AVERAGE('Table'[Production])
    )
    return
    AVERAGEX(
        FILTER(
            t,
            'Table'[Organisation]=_organisation
        ),
        [Avg]
    )
)
return
SUMX(
    tab,
    [Forecast]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello Allan,

Thank you ever so much for your reply. I beleive with Hierarchical Time Series Forecasting, the forecasts are just simply summed, not averaged, to arrive at a forecast for the overaching organisation. Thank you for providing the code, I am new to Power BI and still learning!

All the best,

North_C30

@v-alq-msft 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

If you want to sum the result, you may try the following measure. Actually, practice makes perfect. The pbix file is attached in the end.

ForecastResult = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Organisation],
    "Forecast",
    var _organisation = [Organisation]
    var t=
    SUMMARIZE(
        ALL('Table'),
        'Table'[Organisation],
        'Table'[Name],
        "Sum",SUM('Table'[Production])
    )
    return
    SUMX(
        FILTER(
            t,
            'Table'[Organisation]=_organisation
        ),
        [Sum]
    )
)
return
SUMX(
    tab,
    [Forecast]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous Can you share sample data and sample output in table format?

[Anonymous]

Hi, I hope this example is useful.

Organisation          Name            Year           Production

1                             Facility 1       2016           40000

1                             Facility 1       2017           38891

1                             Facility 1       2018           36670

1                             Facility 1       2019           36794

1                             Facility 2       2016           50000

1                             Facility 2       2017           50450

1                             Facility 2       2018           49999

1                             Facility 2       2019           49980

With this data, I would generate production forecasts for 2020 for each facility (i.e. 1 and 2). I want to aggreate them to generate a forecast for the parent organisation (organisation 1). 

@amitchandak