Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Subtraction between more than 3 columns

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

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

Line ValueMatl_CostLab CostOH_CostImp_DutyFreightVariance
18,340.0011,546.085,841.061834.0657.62103.3373,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
Need Help

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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
  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Apologies, new to the forum.

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

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Anonymous you can share using one drive/google drive.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Can you share some sample data?

     

    Best Regards,

    Jay