Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
arneman
Regular Visitor

Circular Data Reference

Hi there, hope you can help me out with this issue.

 

  • The DB looks as follows:
  • tblCustomer (ID, Name, Location)
  • tblProject (ID, Name, CustomerName)
    • Linked to Customer on Name
  • tblIResource (ID, Name, DayCost, HourCost)
    • Hourcost is calculated by dividing Daycost by 8
  • tblTimesheet (ID, ProjectID, ResourceName, Hours, Cost, Month)
    • Linked to Project on ProjectID
    • Linked to Resource on Name
    • TimesheetCost is calculated by multiplying Hours by tblResource[Cost]
  • tblInvoice (ID, ProjectID, Month, Cost, Sales)
    • Linked to Project on ProjectID
    • ProjectSales is entered manually
    • ProjectCost is calculated by the sum of all Timesheet rows that match the ProjectID and Month

 

Now what I would like to do is create 2 new columns in tblInvoice. One will show me the difference in ProjectSales and ProjectCost, my profit or loss so to speak. The 2nd one needs to show the relative profit or loss margin in %.

 

However using just a simple Column1 - Column2 does not work. I've tried several option using Calculate() but no success. I keep receiving Circular Dependancy errors. Can anyone help me out?

2 REPLIES 2
v-qiuyu-msft
Community Support
Community Support

Hi @arneman,

 

Please try to create a column to return profit for each project like below:

 

ProfitPerProject = CALCULATE(SUM('tblInvoice'[Sales])-SUM('tblInvoice'[Cost]),FILTER(ALL(tblInvoice),'tblInvoice'[ProjectID]=EARLIER(tblInvoice[ProjectID])))

 

ProfitMarging = DIVIDE('tblInvoice'[ProfitPerProject],CALCULATE(SUM('tblInvoice'[Sales]),FILTER(ALL(tblInvoice),'tblInvoice'[ProjectID]=EARLIER(tblInvoice[ProjectID]))))

 

a3.PNG

 

You can take a look at attached .pbix file.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi

 

Thanks for the reply. Using Calculate and Filter it seems to be functioning. However I seem to be stuck in my database design. The thing is I have several ongoing projects per client which each have a set of costs and items to invoice which I can export/Import from other tooling. On the other side there is the timesheet cost which has to be deducted from the total profit and which I currently enter manually in PowerBI. What I'm hoping to achieve is to see per customer an overview of sales and cost per month AND a second overview with the absolute and relative margins on the total turnover.

 

Can anyone point me in the right direction here?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.