Subtraction between more than 3 columns

[Anonymous]

Hi, I am trying to get a value from the following example

The report is called 'Proposed TC Despatch Margins Report'[

Line Value	Matl_Cost	Lab Cost	OH_Cost	Imp_Duty	Freight	Variance
18,340.00	11,546.08	5,841.06	1834.06	57.62	103.33	73,968.45

The Dax formula I am using is: (Which produces the expected value of 17,731.55)

Std. Cost = Sum('Proposed TC Despatch Margins Report'[Matl_Cost])+Sum('Proposed TC Despatch Margins Report'[Lab Cost])+Sum('Proposed TC Despatch Margins Report'[OH_Cost])+Sum('Proposed TC Despatch Margins Report'[Imp_Duty])+Sum('Proposed TC Despatch Margins Report'[Freight])

To get the 'Variance' I am using the following :

Variance = [Line_Value]-Calculate([Std. Cost]). The result produced is 73,968.45 where it should be 608.48

This has bee driving me nuts over the passed day, trying numerious variations in trying to acheive the expected result with no joy

[Anonymous]

Hi @Anonymous ,

Can you share some sample data?

Best Regards,

Jay

[parry2k]

@Anonymous you can share using one drive/google drive.

[parry2k]

@Anonymous very hard to debug, can you share pbix with sample data, remove sensitive information before sharing.

[Anonymous]

Apologies, new to the forum.

I have the pbix file. How can I share with you?.

[parry2k]

@Anonymous are you adding variance as a measure or a column:

it should be a measure, assuming Line_Value is a column, you need to use aggregate function 

Variance = SUM([Line_Value])-[Std. Cost].

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi, Thanks for getting back to me so quick, much appreciated. 

I have tried using both a meassure and adding a column using many variations of the the folmula. Below is one I have tried currently:

Variance = Sum('Proposed TC Despatch Margins Report'[Line_Value])-Sum('Proposed TC Despatch Margins Report'[Matl_Cost])+Sum('Proposed TC Despatch Margins Report'[Lab Cost])+Sum('Proposed TC Despatch Margins Report'[OH_Cost])+Sum('Proposed TC Despatch Margins Report'[Imp_Duty])+Sum('Proposed TC Despatch Margins Report'[Freight])

It keeps giving the same incorrect result result. 

I had a good head of hair before starting