Forum Discussion

arneman's avatar
arneman
Regular Visitor
9 years ago

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 (I...
Need Help
v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

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]))))

 

 

You can take a look at attached .pbix file.

 

Best Regards,
Qiuyun Yu

  • arneman's avatar
    arneman
    Regular Visitor
    9 years ago

    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?