cancel
Showing results for
Did you mean:
Regular Visitor

## Calculated column vs measure

Hi

Referring to the below table, the column [Predicted Cost] is based upon the following measure:

Predicted Cost = SUM('Programme Resource Planning'[Hours]) * SUM('Programme Resource Planning'[Cost]) * 2.11

The column [Column] is based upon the following calculated column:

Column = 'Programme Resource Planning'[Hours] * 'Programme Resource Planning'[Cost] * 2.11

Why am I getting different values?

3 ACCEPTED SOLUTIONS

Accepted Solutions
Senior Member

## Re: Calculated column vs measure

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)

Senior Member

## Re: Calculated column vs measure

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.

Regular Visitor

## Re: Calculated column vs measure

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

5 REPLIES 5
Highlighted
Regular Visitor

## Re: Calculated column vs measure

Sorry, I probably should have asked "how do I turn the calculated column into a measure"

Super Contributor

## Re: Calculated column vs measure

when you drop new measure down you choose between column or measure.  in order to change from column to measure you need to paste the calculation into a new measure, in a measure you need to apply aggregate functions as in your first calulation below.

why its throwing an error is very difficult to know without seeing your base data

Proud a to be a Datanaut!
Senior Member

## Re: Calculated column vs measure

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)

Senior Member

## Re: Calculated column vs measure

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.

Regular Visitor

## Re: Calculated column vs measure

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

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)