Best Practice for Data Model: 1 transaction, multiple items

[anthonyb]

Hi all, within this topic I’m looking for opinions on the most efficient method to setup by Data Model.

Through my investigations into Enterprise DNA Fact Table and Data Model examples, most have the following commonalities:

• Fact Table (aka: Sales Table)
  1 Row = 1 Transaction = 1 Customer = 1 Product = x product quantities.

This understanding has led me to review my Fact Table and identify differences between the fundamentals of how my organisations data differs from the examples.

• Fact Table 1 (aka: SaleTransactions)
  1 Row = 1 Transaction = 1 Customer = 1 SaleID
• Fact Table 2 (aka: SaleDetails)
  x rows = 1 SaleID = x products = x product quantities

Where I presume a “one-to-many” relationship of “SaleTransactions-to-SaleDetails” should exist; Filter Tables of Date, Location, Guests etc all are “one-to-many” which cascade (waterfall) into the “SaleTransactions” table.

This image is a display of the described Data Model.

1. Do any of you have similar Data Models?
2. Do you see anything wrong with the current layout?
   e.g. Cross filter direction of “SaleTransactions-to-SaleDetails”, should be BOTH not single?

Hurdles I face with this Data Model:

• Products are recorded in “SaleDetails” which needs a Filter Table connected.
  
  

3. Do you believe I’m still on the right path here?

• Transaction vs Item level: Discounts and Redemptions
  • Scenario 1 (Text based)
    A customer purchases 3 items in 1 visit and gets a discount on 1 product and a redemption on the total transaction price.
    • Their transaction is recorded on two tables.
      1 row in “SaleTransactions” for the completed transaction with redemption off total
      3 rows in “SaleDetails” one for each purchased item 1 with its discount off it’s line total
  • Scenario 2 (PBI File)
    Same structure as ‘Scenario 1’ but with sample data from organisation
    anthonyb.DemoData.pbix (190.0 KB)

---

This should be simple right? From the courses and insights Sam has shared I have so many methods of connecting and calculating this data.

4. I am looking for a simple, power efficient and accurate measure to get a [Total Sales] measure that works in all contexts: Dates, Products, Customers, Locations, Users.

[Paul]

Hi,

In general avoid many to many relationships, merge the tables if possible in PQ to create 1 flattened table. Also in your model you use SUMX many times. This is not required in this case. SUM will do the job. For 484705, you selected Name as well, so in the row context PBI will show the values for each Name.

Paul

[sam.mckay]

Ok so not bad on the model here,

Paul is right here you want to merge these tables if they make sense.

But you could probably clean this up a bit more because you probably don’t need all the columns in each table once the merge is done.

Also you definitely do not want the multi directional relationship.

This to me is all you need…as simple as can be

Then consider which column are required here.

You already have the date in another column so this isn’t needed.

Your sales totals are already in the line total column and can be calculated with a simple SUM now

See how you go with all these ideas.

The key is to simplify simplify simplify.

The more you clean up here the easier everything else becomes when running calculations with DAX measures.

Thanks
Sam

[EnterpriseDNA]

Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.