Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dan_yoxall
Helper I
Helper I

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?

 

Capture.PNG

3 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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)

 

 

View solution in original post

mattbrice
Solution Sage
Solution Sage

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. 

View solution in original post

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

View solution in original post

5 REPLIES 5
mattbrice
Solution Sage
Solution Sage

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. 

Anonymous
Not applicable

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)

 

 

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

vanessafvg
Super User
Super User

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 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




dan_yoxall
Helper I
Helper I

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.