Order / invoice data modelling

Question

I've always struggled with order and invoice tables. My data has four important fact tables:

Order line details

Order header

Invoice line details

Invoice header

Some of the data in the header tables is redundant but every table contains enough to be too useful to skip out. To make life easier... If I could denormalise these into one table it'd be wonderful but that seems more complicated in other ways. You have several hurdles:

orders split over multiple invoices
invoices with no order data (eg. refunds)
actual order lines split over multiple invoices
potentially multiple orders per invoices (though I don't think this occurs in my data)

I've settled on just denormalising into two tables:

Orders

Invoices

Working with just the invoices table is easy... Everything is finalised and easy to make relationships and measures from. Orders on the other hand is a pain. I'm not sure how I should relate this table to the others. Here's what I have been using recently:

Most of my work was done just using the invoices table previously but now I'm using that orders table more and more (calculating complete/incomplete orders, time to completion, etc).

The problems arise when there isn't an invoice for an order. What is the best way to get around this? I started using LOOKUPVALUE to pull item and customer information... I've had to make all the filtering single direction though (it seems to interfere and I don't know why, I thought LOOKUPVALUE was meant to ignore relationships). I'm not sure if this is the best way.

emarc1 · Answer

I've been chipping away at the Ferrari/Russo 'Analyzing Data with Microsoft Power BI and Power Pivot for Excel' book which has some advice on this. It suggests to use:Customers &lt;&gt; Orders &lt;&gt; OrdersInvoicesBridge &lt;&gt; Invoices&nbsp;Orders linked with the bridge via an Order Number &amp; Line key.Invoices linked with the bridge via an Order Numer &amp; Line &amp; Invoice Number key.Quantities invoiced can be held in the bridge table.&nbsp;As my company never puts multiple orders on a single invoice, I think I can actually skip out the bridge table so:&nbsp;Items / Customers &lt;&gt; Orders &lt;&gt; Invoices&nbsp;It doesn't solve my issue of Invoices without Orders but I think I will have to DAX my way around that issue. I will update this once I nail&nbsp;more issues down.

emarc1 · Answer

I think I have finally come up with a solution to when there are invoices that do not have an order number attached...&nbsp;&nbsp;In short, the solution is to just append the important data from the invoices table into the orders table so data can then flow from the dimensions through that.&nbsp;To do this:For the invoice table:Fill the blank order number fields with the invoice number instead (and add a prefix so there's no possibility of a data clash).For the order table:Make a new table&nbsp;with invoice data for just the invoices with no order number and add the essential columns for dimension table links (eg. customer and item codes, dates, etc) and the order number (with the same prefix as above).Append this table to the orders table.This doesn't affect any other data and allows filtering to flow properly. I think it'd also be possible with a bridge table but it's a rare occurence for us and it's possible that a bridge table could create larger performance issues.

v-qiuyu-msft · Answer

The problems arise when there isn't an invoice for an order. What is the best way to get around this? I started using LOOKUPVALUE to pull item and customer information... I've had to make all the filtering single direction though (it seems to interfere and I don't know why, I thought LOOKUPVALUE was meant to ignore relationships). I'm not sure if this is the best way.
&nbsp;
Hi&nbsp;emarc1,
&nbsp;
If this is no invoice record for the order, which result you want? Can you clarify it?&nbsp;
&nbsp;
It would be better if you could share some sample data and show desired results for our analysis.&nbsp;
&nbsp;
Best Regards,Qiuyun Yu&nbsp;

emarc1 · Answer

Thank you for your reply v-qiuyu-msft.&nbsp;Scenario:An order is placed and appears in the 'orders' table.The 'order' table contains the customer and item codes.This order has not yet been invoiced yet so there is not a line in the 'invoices' table with a related order number.Therefore, in this data model, there is no relationship between this row of the&nbsp;'orders' table and the 'customers' and 'items' dimension tables.&nbsp;What is the best way to get the customer and item dimensions for this order?&nbsp;Is a better data model structure possible?&nbsp;To get around this (for now), I have used LOOKUPVALUE but it doesn't work unless I turn off some bi-directional filtering. Is that normal? Why does LOOKUPVALUE get affected by filtering?

v-qiuyu-msft · Answer

Hi&nbsp;emarc1,
&nbsp;
You can follow these two links to do data modeling:
&nbsp;
https://www.red-gate.com/simple-talk/sql/bi/power-bi-data-modelling/&nbsp;
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/
&nbsp;
Looupvalue() function should work&nbsp;well with both cross filter. If you don't turn off it, is there any error throws out when you use Lookupvalue() function?&nbsp;
&nbsp;
Best Regards,Qiuyun Yu&nbsp;

Forum Discussion

Order / invoice data modelling

6 Replies

Related content

PDF Invoice

Lakehouse vs Semantic Model Column order

V-Order & Z-Order

Ascending order

Top 5 in descending order