cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
badger123 Member
Member

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

Accepted Solutions
badger123 Member
Member

Re: Weighted Average Seasonality

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 Member
Member

Re: Weighted Average Seasonality

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 294 members 3,207 guests
Please welcome our newest community members: