Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there, hope you can help me out with this issue.
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?
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
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?
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |