Subtract Column cell value from another Calculated column cell value

[Anonymous]

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!

[Anonymous]

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]

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]

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.

[Anonymous]

Can RELATED or LOOKUPVALUE use data filters to determine which NNP to bring from another table? As from my example those NNP values are different for the same product but for different time frames. 

[Anonymous]

This is the Expression you should use

NNP [Calculated Column]:
Sales[Sales]- CALCULATE(MAX(NNP),TREATAS(SUMMARIZE(Sales, Sales[Product],Sales[Date]),Cost[Procut],Cost[Date]))

Run this Expression in the Sales Table and you should have the Answer.

Mark as Solution if the Issue is Solved.

[Anonymous]

Unfortunately it doesn't work. It says there is an expression error. I tried manupulating the formula you suggested in different ways but still come up with some bad result. As I understand from your formula, there is no NNP dependency on sales date. As in my example NNP for individual sale depends on the date.

[Anonymous]

I think you would need to Streamline this Data a bit more. For Instance, In your Example You have Mobile Phone dated to 1st and 16th July ( From Field ) Only in the Cost Table but in the Sales Table (3rd row) dated as (03/07/19) you have pulled 500 as a LookupValue which is Wrong because in the Cost table mobile Phone is dated as  (01/07/2019) & (16/01/2019).

[Anonymous]

I double checked and see that the data is correct. I will elaborate a little bit on this:

Table SALES gives all sales data - what product was sold, which date and what was the sales amount. This table changes upon each sales operation.

Table COST gives information what were the actual costs of a particular product on a given date. It is like a fixed pricelist which will not change unless I agree new sales conditions with the supplier.

So for Mobile phone we have a cost of 500 on dates from 07.01 to 07.15 (15th included) and 480 from 07.16 to 07.31. I used a calculated column to pull data from COST table to SALES table. But cannot use caculated column as a reference to create new calculated columns.

[Baskar]

can you please share the power BI file with sample data. I will work for you.

[Anonymous]

Hi,

sure. Sharing: Budget Power BI