Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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]))
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |