Forum Discussion

GiaD30's avatar
GiaD30
Helper II
1 year ago
Solved

Weighted average by month

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 

 

DateColumn BColumn Cweighted average
2/3/2024101001.052631579
2/4/2024151201.894736842
2/5/2024232004.842105263
2/6/2024122102.652631579
2/7/2024113203.705263158
3/3/2024222505.97826087
3/4/2024402008.695652174
3/5/2024451155.625
3/6/2024331374.914130435
3/7/202492182.132608696
Need Help

9 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    1 year ago

    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's avatar
      GiaD30
      Helper II
      1 year ago

      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
  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

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

  • GiaD30's avatar
    GiaD30
    Helper II
    1 year ago

    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.

     

    DateColumn BColumn Cweighted average
    2/3/2024101001.052631579
    2/4/2024151201.894736842
    2/5/2024232004.842105263
    2/6/2024122102.652631579
    2/7/2024113203.705263158
    3/3/2024222505.97826087
    3/4/2024402008.695652174
    3/5/2024451155.625
    3/6/2024331374.914130435
    3/7/202492182.132608696
    • GiaD30's avatar
      GiaD30
      Helper II
      1 year ago

      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's avatar
      GiaD30
      Helper II
      1 year ago

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