I’m looking for guidance on how to build and organize measures when I have multiple fact tables.
My company is a drop-shipper. We have several different transactions that occur with a given order.
We can book an order on January 1 and not invoice the order until March 1. In some cases, we invoice specific lines on an order when they arrive at the destination, so there may be multiple invoices on a given sales order. Management group likes to analyze in different ways depending on the scenario. Sometimes they want to know everything that’s booked (what’s in the ‘pipeline’?), but sometimes they only want to know what’s been invoiced (what money is already collected).
As a result, I have multiple fact tables to work with;
- Sales Orders
- Sales Invoices
Technically I also have
3. Purchase Orders
4. Purchase Invoices
5. Sales Return Orders
6. Sales Credit Memos
7. Purchase Return Orders
8. Purchase Credit Memos
… keep this in mind, but I’m not too worried about most of these.
More information on my data structure: A Sales Order can have multiple corresponding Sales Invoices. The Sales Order itself has a field called “Quantity” and a field called “Quantity Invoiced”. I suppose I could build everything on the Sales Orders list and utilize the quantity invoiced to pull invoiced metrics. However, there is the odd case where there is a Sales Invoice that doesn’t have a corresponding sales order for whatever reason. These would not be included in the dataset, which is a problem.
My question is; as I build up measures for the Sales Orders table, do I have to duplicate my work on the Sales Invoices table?
As a simple example, if I build a measure for the Sales Orders table, “Ext. Price”, do I have to create another measure for the Sales Invoices table, “Inv. Ext. Price”? What if I want two sources of Inv. Ext. Price; one from the ‘Sales Orders’[Quantity Invoiced] and one from the ‘Sales Invoices’[Quantity] table?
Any advice on best practices in this scenario would be greatly appreciated.