How to get total values inside aggr()?

[fionna51]

We need to calculate weighted ratings for multiple measures. The formula looks like below.

Rating = Sum( Aggr(MeasureRating * Max(Weight), Measure)) / Sum(Aggr(max(Weight), Measure))

For each measure, each organization, there is a MeasureRating. When select any of Organization, it will show Rating for all measures this organization has. We also want to show the overall rating for the whole network (include all organizations). When select any one organization this overall rating should not change.

I already calculated each MeasureRating for the whole network, however, some organization has less number of measures. If I select this kind of organization, the "Measure" I used inside Aggr() is less than total number of measures the whole network has. Is that possible to use Total function inside Aggr to get all measures?

Please see my attachment, when select o2, it should show same as select o1 or not select anything. (0.82)

[sunny_talwar · Accepted solution]

May be this:

='Network AVG = ' & Sum({<OrgID>}Aggr(Only({<OrgID>}NetworkRating) * Max({<OrgID>}Weight), Measure)) / Sum({<OrgID>}Aggr(max({<OrgID>}Weight), Measure))