Forum Discussion

stb130130's avatar
stb130130
Regular Visitor
3 years ago
Solved

Weighted Average by filtered Category

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
Need Help
  • stb130130's avatar
    stb130130
    3 years ago
    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])
        )
    )

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    stb130130 , I think it should be like

     

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

    • stb130130's avatar
      stb130130
      Regular Visitor
      3 years ago

      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
       
       

       


       

    • stb130130's avatar
      stb130130
      Regular Visitor
      3 years ago
      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])
          )
      )