Forum Discussion
badger123
Resolver I
Resolver IWeighted Average Seasonality
Hello, hoping someone can help with this please. I'm looking to calculate a weighted average of seasonality. Here are simplified tables with some dummy data. I am looking for a measure to calculate seasonality for each "item_id" weighted by "weight".
Seasonality Table
| item_id | region_id | month | seas_component |
| 1 | 1C | January | -3.93547454 |
| 1 | 1D | January | -0.16991384 |
| 1 | 1E | January | -4.4186439 |
| 1 | 1F | January | -6.11735502 |
| 1 | 1G | January | -0.41507024 |
| 1 | 1H | January | 4.548411558 |
| 1 | 1I | January | 2.718233471 |
| 1 | 1J | January | 2.439170349 |
| 1 | 1K | January | -2.43710404 |
| 1 | 1L | January | -7.8258547 |
| 1 | 1M | January | -4.41261574 |
| 1 | 1N | January | -11.8819123 |
| 2 | 1C | January | -3.93547454 |
| 2 | 1D | January | -0.16991384 |
| 2 | 1E | January | -4.4186439 |
| 2 | 1F | January | -6.11735502 |
| 2 | 1G | January | -0.41507024 |
| 2 | 1H | January | 4.548411558 |
| 2 | 1I | January | 2.718233471 |
| 2 | 1J | January | 2.439170349 |
| 2 | 1K | January | -2.43710404 |
| 2 | 1L | January | -7.8258547 |
| 2 | 1M | January | -4.41261574 |
| 2 | 1N | January | -11.8819123 |
Weight Table
| item_id | country | region_id | weight |
| 1 | UK | 1C | 644.1667 |
| 1 | UK | 1D | 2228.333 |
| 1 | UK | 1E | 1641.667 |
| 1 | UK | 1F | 1184.167 |
| 1 | UK | 1G | 2025 |
| 1 | UK | 1H | 1645 |
| 1 | UK | 1I | 8841.667 |
| 1 | UK | 1J | 2575.833 |
| 1 | UK | 1K | 1240 |
| 1 | UK | 1L | 873.3333 |
| 1 | UK | 1M | 1775 |
| 1 | UK | 1N | 555 |
| 2 | UK | 1C | 13.33333 |
| 2 | UK | 1D | 38.33333 |
| 2 | UK | 1E | 26.66667 |
| 2 | UK | 1F | 17.5 |
| 2 | UK | 1G | 34.16667 |
| 2 | UK | 1H | 35.83333 |
| 2 | UK | 1I | 155.8333 |
| 2 | UK | 1J | 50 |
| 2 | UK | 1K | 23.33333 |
| 2 | UK | 1L | 15.83333 |
| 2 | UK | 1M | 28.33333 |
| 2 | UK | 1N | 10 |
I think I have solved it with this measure...
Weighted_Average_Seasonality = DIVIDE( SUMX( ADDCOLUMNS( SUMMARIZE( 'Seasonality Table', 'Seasonality Table'[item_id], 'Seasonality Table'[region_id], 'Seasonality Table'[month], 'Seasonality Table'[seas_component], "av_weight",CALCULATE(AVERAGE('Weight Table'[weight])) ), "test", 'Seasonality Table'[seas_component]*[av_weight] ), [test] ), SUMX('Weight Table','Weight Table'[weight]))
1 Reply
badger123I think I have solved it with this measure...
Weighted_Average_Seasonality = DIVIDE( SUMX( ADDCOLUMNS( SUMMARIZE( 'Seasonality Table', 'Seasonality Table'[item_id], 'Seasonality Table'[region_id], 'Seasonality Table'[month], 'Seasonality Table'[seas_component], "av_weight",CALCULATE(AVERAGE('Weight Table'[weight])) ), "test", 'Seasonality Table'[seas_component]*[av_weight] ), [test] ), SUMX('Weight Table','Weight Table'[weight]))