Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Subtract Column cell value from another Calculated column cell value

Hello,

 

I am new to Power BI and got stuck with one thing in a report. I want to create a sales report which:

1. Links all Sales data to external pricelist which includes additional support from suppliers for certain time periods. Step DONE: used calculated column with filters to find needed value. I called this column [NNP].

2. Then I need to subtract from each column [Sales] value of calculated column [NNP] and if the value is < 0:

    2.1 in new column [Budget] use function ABS([Sales]-[NNP]);

    2.2. in new column [BudgetWriteOff] insert value "Yes".

Whenever I try to subtract values from column [Sales] and [NNP] I get circular reference error.

Has anyone got any ideas how I could solve this?

A big thanks in advance!

Need Help

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

     

    Can you provide us with some Sample Data and the Expected Output. Some Screenshots basically,

     

    Further more If you are looking to Subtract vales from a Table which is not linked to the Current Table, then you would use a DAX function called TreatAs

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

       

      ok so initially I had two tables 'Cost' and 'Sales':

      Then I created a calculated column in table 'Sales' which takes product cost based on sales date and actual cost for that date. Outcome was this:

       

      Now I need to create 2 more columns:

      1. Calculated column [Budget] which calculates [Sales]-[NNP]. If value <0, then use ABS([Sales]-[NNP]). Otherwise cell value is 0

      2. If value in calculated column [Budget] <> 0, cell value should be "Yes", otherwise "No".

      Desired outcome:

      Hope it is clearer now.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I hope you should have used Related or LookupValue for bringing in the Values from the Second Table. Instead of bringing in a New calculated Column to bring the NNP Values into the Sales Table, Can you try to used the LOOKUPVALUE or the RELATED Functions in calculating the Budget.

         

        It should be like IF(Sales- LOOKUPVALUE(NNP)<0,ABS(Sales- LOOKUPVALUE(NNP)),<<ELSE LOGIC>>). Its causing a circular reference because by creating this NNP Calculated Columnn you are creating a Dependency.