Weighted average of measures?

[Anonymous]

I am unable to get a proper weighted average. I have revenue data... The way I try doing it:

RevExclu =
CALCULATE (
    SUM ( Revenue[Revenue] ),
    FILTER ( 'Sectors', NOT 'Sectors'[Sec] IN { "xyz", BLANK () } )
)

(I have to exclude some revenue)

Then I get the total per UP:

TotalPerUP =
SUMX ( SUMMARIZE ( Revenue, 'Customer'[UP] ), [RevExclu] )

Then I get the max Sec per UP. (doesn’t matter which Sec, but the revenue sum value).

MaxSecPerUP =
MAXX ( SUMMARIZE ( Revenue, 'Customer'[UP], 'Sectors'[Sec] ), [RevExclu] )

Then I get the metric which Is total/max:

MetricUp =
DIVIDE ( [TotalPerUP], [MaxSecPerUP], BLANK () )

Then; I should go one level up and do the the weigthed average of this metric… Should be SUM( metric x size)  / Total

The upper level is Lead (each Lead has many UPs)

I have the total:

TotalPerLead =
SUMX ( SUMMARIZE ( Revenue, 'Lead'[Lead] ), [RevExclu] )

Now I need to go each UP by UP and do  (Metric x Size) (and then do a SUM of all that...)

That is, go UP by UP and do [TotalPerUP]*[MetricUp]

(I have only achieved it using ALLEXCEPT, but I would prefer not using it since it removes my filters…) How can I do it?

[v-deddai1-msft]

Hi @Anonymous ,

Would you please show us sample pbix file? I'm not getting clear from your description without sample data.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai