Forum Discussion

v-nankh's avatar
v-nankh
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...
Need Help
v-chuncz-msft's avatar
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's avatar
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