Forum Discussion

Frequent Visitor

8 years ago

Calculating difference between Average(Weightd_Avg) and Average(Previous_WeightedAvg) in Subtotals

I am creating a PowerBI Report with 3 number columns which basically has Weighted Average, Previous_WeightedAvg and Delta. I have calculated all these 3 in SQL. The weighted average and previous_wei...

Community Support

8 years ago

If you are not familiar with the measure, create a calculated table to summarize data first.

Frequent Visitor

8 years ago

v-chuncz-msftThanks! But it's not working.

Can you please elaborate on the measure part?

v-chuncz-msft
Community Support
8 years ago
v-nankh,

Share us a simplified model so that we could provide the suitable formula.
- v-nankh
  Frequent Visitor
  8 years ago
  v-chuncz-msft,
  
  I am getting the value of Weighted_Avg and Pre_WeightedAvg by using the formula from the below link which contains another question of mine.
  
  Weighted and Previous Weighted Avg Calculation
  
  Below is the snapshot from the PowerBI matrix that I currently have.
  Weighted_Avg,Pre_WeightedAvg and Delta I am calculating in SQl but using the below measure because because weighted_avg is a number field but it can have NA:
  Weighted_Avg_NA = var myAvg = AVERAGE(RatingsDelta_NIST[Weighted_Avg]) return if(ISBLANK(myAvg),"NA",myAvg)
  
  Pre_WeightedAvg = var myAvg = AVERAGE(RatingsDelta_NIST[PreviousWeighted_Avg]) return if(ISBLANK(myAvg), "NA",if(myAvg = 0,"",myAvg))
  
  Delta_NA = var myAvg = AVERAGE(RatingsDelta_NIST[Delta])return if(ISBLANK(myAvg),"NA",myAvg)
  In the above fig Business Env is the Category and the rest 1-5 are its subcategories. In Weighted_Avg column value 3.20 is the average os Subcategories weighted_avg = ( 3+2+4+4+3)/5 and similarly for pre_weightedavg.
  Currently the delta formula that I am using is not giving me the right result for Category Difference (Circled in red in above fig).
  
  Any help?
  
  Thanks,
  Nancy