Forum Discussion

Zack92's avatar
Zack92
Icon for Helper III rankHelper III
3 years ago

Weighted Average by Region Calculation not working

Hello 

 

I am trying to get the Weighted Average by Region next to my average DailyRev., but the numbers are really high I could not figure out why is that. Can anybody please check this and let me know what's going on? I spent a lot of hours and I am stumped. I have filtered for the last 12 months, but it should not affect it. 

Formula

Rev BY Region = 
VAR TotalDailyRev = 
     CALCULATE(            
       SUM(Sampledata[DailyRev]),             
       ALL(Sampledata[Region_Adj])
    )
VAR SummerisedTable = 
   ADDCOLUMNS(
      SUMMARIZE(
         Sampledata,
         Sampledata[Region_Adj]
     ),
     "DailyRev", SUM(Sampledata[DailyRev]),        
     "WT", SUM(Sampledata[DailyRev])/TotalDailyRev
   )
RETURN    
  SUMX(
   SummerisedTable,
   [WT] * [DailyRev]
  )

6 Replies

  • Ahmedx 

     

    Thank you for the quick response.

    Not quite right, I looking for the weighted average for each region. Not the %.  I need the 12-month filter. I don't think we need to remove the filter, right 

    Again, thank you 

    • Zack92's avatar
      Zack92
      Icon for Helper III rankHelper III

      Ahmedx 

       

      To be honest, I am not sure. That is why seeking help in Community. I am trying to check the way I calculated my weighted average is correct or wrong.  When Comparing below,  my average and my calculated weighted column, it has a very high number. which I don't think is correct (not 100% sure) .  

       


      I just want to check with you guys if my method is correct. 

       

      Thank you 

  • I can't understand your business logic without the expected numbers.
    But maybe you are waiting for this?!

  •   

    Thanks for the post, but I found the correct way to calculate the weighted average in my situation. Weighted_Avg = ABS(CALCULATE(SUM(AppendCombined[Revenue])/(SUM(AppendCombined[Hours])/8))) 

    This is the correct formula for my situation to get it. Again, thank you for the help 

     

     

     

     

    Ahmedx