I’m working on a sales dashboard with sales per customer, product/itemcode, gross/net sales set off against the unit cost price. Getting the correct cost price is the issue in this case, here are some reasons why.
The company is in the petrochemicals industry which means that there are world market prices deriving from oil prices which change weekly.
The company’s main activity is the manufacturing of different types of plastics.
We also sell products of other companies within the group, based upon consignment stocks. We receive stocks without becoming the owner. We receive a commission of 2.5% for that.
Our financial system does not offer any clear, correct and useable reports which can be dumped to excel and serve as a dimension table in Power BI. One of the reasons is that the correct cost price is only registered when the stock value is financially re-valuated during the months close in the first week of the next month.
So the cost price is depending on several conditions of which I have captured some in below statement
Have a look at my data model below. These are all SQL tables from the ERP system.
Dimension tables at the top are from left to right, customer table, dates table and product table.
The facts table at the bottom are Salesdata (turnover, per order/invoice, per product etc), Forecast table and the cost price history table. The last one is a query which I wrote in the database to get the cost prices.
Have a look at a piece of the report I’m building below
You can see 2 cost price columns, the left one being the result of the formula posted above, the other one is the consignment cost price which is 97.5% of the sales price.
My question is, how do I get these prices into one cost price column?
The fact tables contain the products sold but the product dimension table contains the typename which indicates whether it’s a manufactured product or a consignment product.
The variable statement does not let me calculate sales with a condition from a dimension table, or I simply don’t know how.
I have tried this with the Calculate formula, with a virtual table but nothing works. Of course I could add a calculated column to the table with the query editor, but if I remember correctly some of the first lessons from Sam is, DON’T DO THAT!! because you don’t need it. I hope you can help me find another way.
This is my first post so please feel free to let me know if I should elaborate or do something differently in asking my question.