Weighted Average by filtered Category

[stb130130]

Hello, 

I am trying to create a weighted average based on a filtered measure from a live connected model. As of now, I have created the below measure but am having trouble aggreating the total of weights to aggregate by PM. I am instead getting individual values that total to the weights which does make sense. That said, is there a way to bring in the total vs the individual amounts being calcualted. 

My aim is to get to the 132,014 seen in the excel snapshot. 

EAC X and EAC Revenue are both measures. 

In Excel Example

 

BI Results

 

Weighting by Project =

VAR ProjectEACRevenue =

CALCULATE(

    [.EAC Revenue],

    ALLEXCEPT(Projects,Projects[Project Number])

)

VAR ProjectManagerRevenue =

CALCULATE(

    [.EAC Revenue],

    ALLEXCEPT(Projects,Projects[Project Manager])

)

VAR Weights =

DIVIDE(ProjectEACRevenue, ProjectManagerRevenue)

VAR Weighting =

Weights *

CALCULATE(

    [X],

    (ALLEXCEPT(Projects,Projects[Project Number])

    )

)

RETURN Weighting

[amitchandak]

@stb130130 , I think it should be like

VAR Weighting =
Sumx
(Values(Projects,Projects[Project Number]),calculate( [X]*Weights)
)

[stb130130 · Accepted solution]

Well I ended up just creating dataset and making the measure. 

Weighted Average  =

SUMX(

    VALUES('PFM'[Project Manager]),

    VAR PMTotalRevenue = SUMX(FILTER('PFM', 'PFM'[Project Manager] = 'PFM'[Project Manager]), 'PFM'[.EAC Revenue])

    RETURN

    SUMX(

        FILTER('PFM', 'PFM'[Project Manager] = 'PFM'[Project Manager]),

        DIVIDE('PFM'[.EAC Revenue], PMTotalRevenue) * RELATED('X'[X])

    )

)

[stb130130]

Thanks - I tried it and am getting the same values as I was before. I think I might need to so sumx filtered as of the variables as I am not bringing in values when I remove project number. 

Weighting by Project =

VAR ProjectEACRevenue =

CALCULATE(

    [.EAC Revenue],

    ALLEXCEPT(Projects,Projects[Project Number])

)

VAR ProjectManagerRevenue =

CALCULATE(

    [.EAC Revenue],

    ALLEXCEPT(Projects,Projects[Project Manager])

)

VAR Weights =

DIVIDE(ProjectEACRevenue, ProjectManagerRevenue)

VAR Weighting =

Sumx

(Values(Projects[Project Manager]),calculate( [X]*Weights)

)

RETURN Weighting