Weighted average by month

[GiaD30]

Hello everyone,

Please, can you guide/help me?

I have the below table and I need to calculate the weighted average by month, the formula in excel for the Weighted average by month = (Column B*Column C) /Total Sum column C for each month.

How can I calculate Weighted average using 

Date	Column B	Column C	weighted average
2/3/2024	10	100	1.052631579
2/4/2024	15	120	1.894736842
2/5/2024	23	200	4.842105263
2/6/2024	12	210	2.652631579
2/7/2024	11	320	3.705263158
3/3/2024	22	250	5.97826087
3/4/2024	40	200	8.695652174
3/5/2024	45	115	5.625
3/6/2024	33	137	4.914130435
3/7/2024	9	218	2.132608696

[v-pnaroju-msft · Accepted solution]

Thank you, @pankajnamekar25 , @bhanu_gautam , @ThxAlot , and @Ashish_Mathur , for your responses.

Hi @GiaD30,

Please find attached the screenshots and PBIX file, which may help in resolving the issue:

If you find our response helpful, kindly mark it as the accepted solution and give kudos. This will assist other community members facing similar queries.

Thank you.

[GiaD30]

Thank you very much everyone for all the help, it is working now.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[ThxAlot]

[GiaD30]

@ThxAlot  thank you so much,now I am asking, what if we need to consider other columns, like category, sub category and month/year. 

I have added extra columns and would like to attach again the pbi, but don;t know how.

[GiaD30]

@bhanu_gautam thank you so much for your reply, unfortunately, the result is the same with the numbers from column B, I also tried something similar.

I need the result from column Weighted average from my table below.

Date	Column B	Column C	weighted average
2/3/2024	10	100	1.052631579
2/4/2024	15	120	1.894736842
2/5/2024	23	200	4.842105263
2/6/2024	12	210	2.652631579
2/7/2024	11	320	3.705263158
3/3/2024	22	250	5.97826087
3/4/2024	40	200	8.695652174
3/5/2024	45	115	5.625
3/6/2024	33	137	4.914130435
3/7/2024	9	218	2.132608696

[bhanu_gautam]

@GiaD30 

Create a new column for the weighted value: This column will be the product of Column B and Column C.

WeightedValue = [Column B] * [Column C]

Create a new measure for the total sum of Column C by month: This measure will calculate the total sum of Column C for each month.

TotalSumColumnC = CALCULATE(SUM('Table'[Column C]), ALLEXCEPT('Table', 'Table'[Date].[Month]))

Create a new measure for the weighted average by month: This measure will calculate the weighted average using the previously created columns and measures.

WeightedAverage = DIVIDE(SUM('Table'[WeightedValue]), [TotalSumColumnC])

[pankajnamekar25]

Hello @GiaD30 

You can try this measure

Weighted Average by Month =

VAR TotalWeightedSum =

    SUMX(

        FILTER( 'Table',

            MONTH( 'Table'[Date] ) = MONTH( MAX( 'Table'[Date] ) )

            && YEAR( 'Table'[Date] ) = YEAR( MAX( 'Table'[Date] ) )

        ),

        'Table'[Column B] * 'Table'[Column C]

    )

VAR TotalWeight =

    SUMX(

        FILTER( 'Table',

            MONTH( 'Table'[Date] ) = MONTH( MAX( 'Table'[Date] ) )

            && YEAR( 'Table'[Date] ) = YEAR( MAX( 'Table'[Date] ) )

        ),

        'Table'[Column C]

    )

RETURN

DIVIDE( TotalWeightedSum, TotalWeight, 0 )

Thanks,
Pankaj

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[GiaD30]

@pankajnamekar25 Thank you so much for the help, unfortunately, using the code above, exactly like this, is not working...

    MONTH( 'Table'[Date] ) = MONTH( MAX( 'Table'[Date] ) )

            && YEAR( 'Table'[Date] ) = YEAR( MAX( 'Table'[Date] ) )

        ),

        'Table'[Column B] * 'Table'[Column C]    here this part is not allowing me