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

[v-nankh]

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_weightedAvg is calculated based on the rating and weight for each NIST category and subcategory against Microsoft's various Services when the service was refresh currently and the last time. The rating could be "1,2,3,4,NA"

Currently this report is being generated manually in excel. Please find below the view:

 Asset Management is the category and it has 6 subcategories. Eac of them have respective values for Weighted_Avg, Previous_WeightedAvg and Delta (Weighted_Avg - PreviousWeighted_Avg). The number in color blue is the Average of (Weighted_Avg), number in green is the Average of (Previous_WeightedAvg). I can obtain these numbers easily in PowerBI by selecting Average and Subtotals. However the number in yellow is not the average of (Delta). It is the difference between the number in blue and number in green. 

I have to create same view in PowerBI matrix. It is possible to have Average of numbers in 2 columns and difference in the third column  (Delta) at Subtotal level?

Please help!

Thanks,

Nancy

[v-chuncz-msft]

@v-nankh,

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

[v-nankh]

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

Can you please elaborate on the measure part?

[v-chuncz-msft]

@v-nankh,

Share us a simplified model so that we could provide the suitable formula.

[v-nankh]

@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

[v-nankh]

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_weightedAvg is calculated based on the rating and weight for each NIST category and subcategory against Microsoft's various Services when the service was refresh currently and the last time. The rating could be "1,2,3,4,NA"

Currently this report is being generated manually in excel. Please find below the view:

 Asset Management is the category and it has 6 subcategories. Eac of them have respective values for Weighted_Avg, Previous_WeightedAvg and Delta (Weighted_Avg - PreviousWeighted_Avg). The number in color blue is the Average of (Weighted_Avg), number in green is the Average of (Previous_WeightedAvg). I can obtain these numbers easily in PowerBI by selecting Average and Subtotals. However the number in yellow is not the average of (Delta). It is the difference between the number in blue and number in green. 

I have to create same view in PowerBI matrix. It is possible to have Average of numbers in 2 columns and difference in the third column  (Delta) at Subtotal level?

Please help!

Thanks,

Nancy