weighted average calculation

• dterrie

  Old Hand

  Points: 359

  More actions

  April 1, 2005 at 10:39 am

  #109647

  I have a fact table similar to the one created with the script below. There are dimensions for the ID and GroupID. In SQL, it's easy to rollup a weighted average, but being a newbie to MDX, I have yet to figure out how to create a calculated member to get the same result in AS. I have tried a variety of formulas but the value for the straight sum/count average and the attempted weighted average using a formula like the one below generate the same output. Can someone set me straight?

  ---

  create table #mytable

  (ID int,

  GroupID int,

  Takers int,

  Score decimal(6,1))

   

  set nocount on

   

  insert into #mytable (ID, GroupID, Takers, Score)

  values (1,1,10,10)

  insert into #mytable (ID, GroupID, Takers, Score)

  values (1,2,20,20)

   

  select ID,

  GroupID,

  Sum(Takers) as SumTakers,

  Avg(Score) as AvgScore,

  sum(Takers * Score) / sum(Takers) as WeightedAvgScore

  from #mytable

  group by ID, GroupID

  with rollup

  having grouping (id) = 0

   

  drop table #mytable

  set nocount off

  ---

   

• Tejal Sumeet Deshpande

  SSC-Addicted

  Points: 428

  More actions

  April 8, 2005 at 4:19 am

  #551410

  hi,

  When you calculate for weighted average as

   sum({{[Measures].[Takers]}*{[Measures].[Score]}}) /

  count({[Measures].[Takers]})

  Even if syntax is correct,it will give you Formula error -duplicate dimension across(independent) axes.

  so to calculate this

  first calculate and name this measure as WeightedAvgScore1

  [Measures].[Takers]*[Measures].[Score]

  then calculate the weighted average

  as

  sum

  ({[Measures].[WeightedAvgScore1]}) / sum({[Measures].[Takers]})

  This will give you as same result as you got with T-sql query.

  Then make WeightedAvgScore1 visible=false.

  HTH

• VUQTRAN

  Valued Member

  Points: 57

  More actions

  March 3, 2006 at 7:26 am

  #624606

  What if there are Null Variables say for the MeasureScore?

  How will this calc code compute?  Esp. if I want to not count NULL as 0.