SUMX() Help

[edwardwe]

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 Hours' = ('OrigEstHours' / 'OrigEstUnits') * 'Actual Units'

My goal is to use the ratio of (OrigEstHours / OrigEstUnits) and multiply it by every 'Actual Units' value and sum that product. For the entire data set, when the work is complete and the 'Actual Units' are much less than the 'OrigEstUnits', I get an incorrect result with the equation below and I understand why.

Earned Hours - Total = DIVIDE(sum(udJCWIPCashFlow_VS[OrigEstHours]), sum(udJCWIPCashFlow_VS[OrigEstUnits]))

* sum(udJCWIPCashFlow_VS[Actual Units])

When I tried using SUMX, as shown below, I still get an incorrect value:

Earned Hours - Total = DIVIDE(sum(udJCWIPCashFlow_VS[OrigEstHours]), sum(udJCWIPCashFlow_VS[OrigEstUnits]))

* SUMX(udJCWIPCashFlow_VS, udJCWIPCashFlow_VS[Actual Units])

[edwardwe]

[saraMissBI]

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,

[Anonymous]

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]

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.