Hey community,
I'm running into an issue when creating new calculated columns.
Example
So for my example I'm reviewing campaign results. So for every campaign we've had a certain media cost and sales amount (number of products sold). Basically we're calculating cost of goods sold in order to determine the added value of the media campaign.
What do I have?
I have 2 datasheets:
- 1 containing delivery data (media cost/sales amount etc. per day)
- 1 containing metadata (campaign name, goal, budget etc.)
What do I want?
I want to check whether a campaign is performing below or above the goal on a certain date based on cumulative values (so Average Cost/Sales to date). Then I want to show how many campaigns are reaching their goal and how many are not. So basically I want to check if the value of the Average Cost/Sales to date on a certain date is below the value of Goal on another data sheet (linked by campaign ID). If it's smaller or equal than the goal I want it to show a "1", so I can sum these up later. This would be the Positive_goal column. In another column I want to show a "1" when it's higher than the goal. This would be the Negative_goal column. This way I could select a certain time span and show a bar chart to show how many campaigns are reaching their goal.
I already tried this:
Positive_goal = if(Final_delivery[Average Cost/Sales to date]<=LOOKUPVALUE(Final_metadata[Goal];Final_metadata[campaignid];Final_delivery[campaignid]);1;0)
Negative goal = if(Final_delivery[Average Cost/Sales to date]>LOOKUPVALUE(Final_metadata[Goal];Final_metadata[campaignid];Final_delivery[campaignid]);1;0)
The first one works, but when I want to create the second column PowerBI tells me there's a circular dependency and it cannot create the new column. Can anybody help me find another way to achieve this?
Thanks in advance
