Forum Discussion

edwardwe's avatar
edwardwe
Frequent Visitor
6 years ago

SUMX() Help

Hello:   A sample of my data set is below. The beige shading is data from Power BI. The blue shading is done in Excel. I am not able to build the logic of the blue cells in Power BI.   'Earned Ho...
edwardwe's avatar
edwardwe
Frequent Visitor
6 years ago

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi edwardwe Please try below measure and check if it works for you

    Measure = 
VAR _ratio = DIVIDE(SUM('Table'[OrigEstHours]),SUM('Table'[OrgEstUnits]),0)
RETURN SUMX('Table',_ratio*'Table'[Actual units])
    • edwardwe's avatar
      edwardwe
      Frequent Visitor
      6 years ago

      Vimal:

       

      Thank you for your respone. The equation you supplied works fine for each line item but the total is incorrect. I have shaded out the Phase & Desc values because this is an active project at my company.

       

      I named your equation 'Earned Hours Rev 6'.

       

      The sum of this value is shown as 7,634 but if you add all the values in the column, it totals 12,535which is the correct value.

       

      The 7,634 value is merely 15,054 / 1,451,624 * 736,124. This is not the same as the values above it because the 'OrigEstUnits' is almost double the 'ActualUnits'.

       

      The second line of your equation does not seem be be following the SUMX() logic.

       

       

  • saraMissBI's avatar
    saraMissBI
    Resolver I
    6 years ago

    Hi edwardwe ,

    Please try the following measure:

     

    Measure = SUMX('Table';DIVIDE('Table'[OrigEstHours];'Table'[OrigEstUnits];0)* SUM('Table'[Actual Units]))
     
    If that works for you, please accept as solution and give kudos!
     
    Best regards,