Forum Discussion
Calculated column vs measure
- Anonymous9 years ago
Predicted Cost = SUM('Programme Resource Planning'[Hours]) * SUM('Programme Resource Planning'[Cost]) * 2.11
Since the above only works 1 line at a time, you need to use a SUMX.
Predicted Cost = SUMX('Programme Resource Planning', CALCULATE('Programme Resource Planning'[Hours] * 'Programme Resource Planning'[Cost]) * 2.11)
- 9 years ago
To answer your question of "Why am I getting different values?". The answer is in the way Dax calcuated your measure. For the respective week of year, it first SUMs up all the 'Hours', then summed up all the 'Cost' values and then multiplied the two numbers together, then muliplied by 2.11.
You can't aggregate unit of measure and cost, then multiply. You have to multiply hours * cost on a line item basis, then aggregate up all the results.
- 9 years ago
Thanks. I realised i needed to use the iterator SUMX in my measure. This gave the correct solution.
Cost Forecast = SUMX( 'Programme Resource Planning', 'Programme Resource Planning'[Hours] * 'Programme Resource Planning'[Cost]) * 2.11
To answer your question of "Why am I getting different values?". The answer is in the way Dax calcuated your measure. For the respective week of year, it first SUMs up all the 'Hours', then summed up all the 'Cost' values and then multiplied the two numbers together, then muliplied by 2.11.
You can't aggregate unit of measure and cost, then multiply. You have to multiply hours * cost on a line item basis, then aggregate up all the results.