Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
badger123
Resolver I
Resolver I

Weighted 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_idregion_idmonthseas_component
11CJanuary-3.93547454
11DJanuary-0.16991384
11EJanuary-4.4186439
11FJanuary-6.11735502
11GJanuary-0.41507024
11HJanuary4.548411558
11IJanuary2.718233471
11JJanuary2.439170349
11KJanuary-2.43710404
11LJanuary-7.8258547
11MJanuary-4.41261574
11NJanuary-11.8819123
21CJanuary-3.93547454
21DJanuary-0.16991384
21EJanuary-4.4186439
21FJanuary-6.11735502
21GJanuary-0.41507024
21HJanuary4.548411558
21IJanuary2.718233471
21JJanuary2.439170349
21KJanuary-2.43710404
21LJanuary-7.8258547
21MJanuary-4.41261574
21NJanuary-11.8819123

 

Weight Table

item_idcountryregion_idweight
1UK1C644.1667
1UK1D2228.333
1UK1E1641.667
1UK1F1184.167
1UK1G2025
1UK1H1645
1UK1I8841.667
1UK1J2575.833
1UK1K1240
1UK1L873.3333
1UK1M1775
1UK1N555
2UK1C13.33333
2UK1D38.33333
2UK1E26.66667
2UK1F17.5
2UK1G34.16667
2UK1H35.83333
2UK1I155.8333
2UK1J50
2UK1K23.33333
2UK1L15.83333
2UK1M28.33333
2UK1N10
1 ACCEPTED SOLUTION
badger123
Resolver I
Resolver I

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]))

View solution in original post

1 REPLY 1
badger123
Resolver I
Resolver I

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]))

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.