Measures with multiple fact tables

DAX DAX Calculations
1

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;

  1. Sales Orders
  2. 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.

2

They are all transactional tables. This is the most common type of fact table. In a transactional table, you have one row per transaction. The grain is at the transaction level.

You could reduce your number of fact tables to 2 or even 1 but that all depends on your data and what you need to showcase in your report. From that perspective would it make more sense to separate Purchase from Sales OR Orders and Returns from Invoices and Credit notes?

However you decide, if you are going to Apend data from different fact tables - spent some time on how to best set that up. Like how to unify the column names (“Ext. Price”, “Inv. Ext. Price”), how to handle quantities (pos. and neg?, remaining- and invoiced?) and amounts so your aggregations make sense.

Ultimately if you have multiple fact tables than yes you’ll need to create separate measures for them.

3

@CStaich essentially you have two fact tables.

An “Order Book” and “Sales Ledger”. Both tables are really separate fact tables but the sales ledger is the master record which should agree to your trial balance.

Note that Sales orders don’t normally impact the accounting numbers as its only a promise to buy something. The Sales invoice table should be reconcilable to your trial balance source document from your accounting system.

In some cases, it may be worthwhile and possible to break up your Sales Invoice and Order fact tables into summary/control tables and detailed tables. This will allow you to reconcile the data faster at the control summary level first.

What system is your data generated from?

When I build Power BI accounting models I build them out as separate component models for the General Ledgers, Customer Sales Ledger or Supplier Purchase Ledger models.

The model operates faster and with dataflows, larger files seem to also get some benefits too.

Anyway, post your model diagram for more help!

Orders don’t really impact an accounting system on the sales side or the balance of the Customer account. The only time it does have an impact is if there is a customer deposit on order then a physical liability is recorded against a customers promise to order some goods.

The Sales Invoice table may need to be broken down into a summary table and then a detailed table containing the line item. It depends how your accounting system is setup

The control ledger just holds the summary data normally and the detailed line items are contained in the invoice sub ledger.

4

Hi @CStaich , we’ve noticed that no response has been received from you since January 11, 2019. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

5

@GarryA & @Melissa

I’m currently trying to refactor these tables in my Data Model. See the screenshot below.

image
image.png923×810 84.2 KB

Highlighted fields are the primary key. Blue carets point to the foreign key that is currently used in the relationship. Pink brackets indicate fields I’m questioning using.

I’ve got a header table and a lines table for each transaction. Foreign dimension keys such as Manufacturer Code and Vendor No. are always on the lines, while some other foreign dimension keys such as Customer No. are always on the header.

I’m curious about whether I should use the “Sales Order Line No” and “Sales Order No” values as part of the data model. I don’t want to create unnecessary relationships in my model if they aren’t going to benefit me.

I could use some validation on this model, as I’m very new and not sure whether I’m heading in the right direction.

Edit: I’m seeing now that I would create circular relationships if I was to connect multiple fact tables to my dimensions. I’m struggling to wrap my head around this problem, and how I might create all the necessary dimension relationships if I have my fact tables connected in this way.

I feel like I’m making some critical mistake that’s keeping me from understanding how to model this.

Edit 2: Except for Sales Invoices, the remainder of my Line tables have a Sales Order No and a Sales Order Line No. I don’t want to create circular relationships between the different fact tables, but perhaps this could be useful. I’m really not sure how to utilize this.

6

I feel I should create a new topic for refactoring my dataset. Thoughts? If I don’t get negative feedback on this idea I’m going to go ahead.

7

@CStaich, a response on this post has been tagged as “Solution”. If you have a follow question or concern please create a new topic. Thanks!