Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ContabilidadBI
Helper III
Helper III

Combine 2 fact tables in M (sales header and sales detail)

Hi!

 

I am working with a model with 2 fact (Sales) tables, One is the header and the other the detail. The thing is that the header has a metric (Discount). I would like to combine this 2 tables in order to recreate a star schema with only 1 fact table. To denormalize the discount from header, in DAX I can create a calculated column in SalesHeader with the following code to have the % discount of the order:

 

SalesHeader[DiscountPct] =
DIVIDE (
    SalesHeader[TotalDiscount],
    SUMX (
        RELATEDTABLE ( SalesDetail ),
        SalesDetail[Unit Price] * SalesDetail[Quantity]
    )
)

 

And then a calculated column in SalesDetail to have the discount in every line:

 

SalesDetail[LineDiscount] =
    RELATED ( SalesHeader[DiscountPct] ) *
    SalesDetail[Unit Price] *
    SalesDetail[Quantity]

 

But how can I do the same procedure in M so I can load into the model only one fact table?

 

Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hello @ContabilidadBI,

 

Now I understood what you need. This I what I got:

Sales TableSales Table

 These are the originals tables:SalesHeaderSalesHeader

 

SalesDetailSalesDetail

  

And this is the explanation, step by step:

1 - Go to SalesDetail and create a new Amount column:New amount column on SalesDetailNew amount column on SalesDetail

Table.AddColumn(#"Changed Type", "Amount", each [UnitPrice] * [Quantity])

 2 - To get the total amount by saleID, duplicate the SalesHeader table, merge it with SalesDetail and sum the column AmountThe table TotalAmountThe table TotalAmount

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0UIrViVYyAjKNIExjINMYwjQBMk2AzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SalesID = _t, TotalDiscount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesID", Int64.Type}, {"TotalDiscount", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"SalesID"},SalesDetail,{"SalesID"},"SalesDetail",JoinKind.Inner),
    #"Expanded SalesDetail" = Table.ExpandTableColumn(#"Merged Queries", "SalesDetail", {"Amount"}, {"Amount"}),
    #"Grouped Rows" = Table.Group(#"Expanded SalesDetail", {"SalesID"}, {{"TotalAmount", each List.Sum([Amount]), type number}})
in
    #"Grouped Rows"

 

 

 

 

 

3 - Merge SalesHeader with TotalAmount table and add a custom column named DiscountPct to it:

SalesHeader table with two new columns - TotalAmount and DiscountPctSalesHeader table with two new columns - TotalAmount and DiscountPct

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0UIrViVYyAjKNIExjINMYwjQBMk2AzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SalesID = _t, TotalDiscount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesID", Int64.Type}, {"TotalDiscount", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"SalesID"},TotalAmount,{"SalesID"},"TotalAmount",JoinKind.Inner),
    #"Expanded TotalAmount" = Table.ExpandTableColumn(#"Merged Queries", "TotalAmount", {"TotalAmount"}, {"TotalAmount"}),
    #"Added Custom" = Table.AddColumn(#"Expanded TotalAmount", "DiscountPct", each [TotalDiscount] / [TotalAmount])
in
    #"Added Custom"

 

 

 

 

4 - And finally, merge SalesDetail with SalesHeader as a new table named Sales and add a custom column named LineDiscount:The Sales TableThe Sales Table

 

 

 

let
    Source = Table.NestedJoin(SalesDetail,{"SalesID"},SalesHeader,{"SalesID"},"SalesHeader",JoinKind.Inner),
    #"Expanded SalesHeader" = Table.ExpandTableColumn(Source, "SalesHeader", {"TotalDiscount", "DiscountPct"}, {"TotalDiscount", "DiscountPct"}),
    #"Added Custom" = Table.AddColumn(#"Expanded SalesHeader", "LineDiscount", each [DiscountPct] * [Amount])
in
    #"Added Custom"

 

Hope it helps!

 

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Hello @ContabilidadBI,

  

This is your data:

SalesHeaderSalesHeader

 

SalesDetailSalesDetail

 

This is the final result using Power Query:

 

SalesSales

And this is the M code:

 

 

let
    Source = Table.NestedJoin(SalesHeader,{"SaleID"},SalesDetail,{"SalesID"},"SalesDetail",JoinKind.Inner),
    #"Expanded SalesDetail" = Table.ExpandTableColumn(Source, "SalesDetail", {"Unit Price", "Quantity"}, {"Unit Price", "Quantity"}),
    #"Added Custom" = Table.AddColumn(#"Expanded SalesDetail", "DiscountPct", each [TotalDiscount] / [Unit Price] * [Quantity]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "LineDiscount", each [DiscountPct] * [Unit Price] * [Quantity])
in
    #"Added Custom1"

 

Explanation:

 

1 - Merge SalesHeader and SalesDetail in a new one called Sales

2 - Expanded the columns Unity Price and Quantity

3 - Added the custom column DiscountPct -> [TotalDiscount] / [Unit Price] * [Quantity]

4 - Added the custom coumn LineDiscount -> [DiscountPct] * [Unit Price] * [Quantity]) 

 

Hope it helps!

Thanks for your response but the relationship is not 1-1 is 1-n

 

This is Sales detailThis is Sales detailThis is Sales HeaderThis is Sales Header
Numticket is the key that relates the 2 tables. The Header table has 1 order per row. The Detail table has normally many rows for 1 order because each row corresponds to the product sold.

 

Thanks!

Anonymous
Not applicable

@ContabilidadBI, sorry for my mistake. But this example also works with one to many relationship.

 

SalesHeaderSalesHeader

 

SalesDetailSalesDetail

 

SalesSales

 

 

 

Hi @Anonymous,

 

I think I am not explaining myself because in your example, the order 1 has a total discount of 10 $ and in the detail it has 2 lines, so lineDiscount should be 1.66666666 $ in the first row and 8.33333333 $ in the second. What I want is to incorporate the total discount (which is in the header) into the detail, in order to use only 1 table as the fact table with all the data, because if I use only the detail, the total sales wouldn't be correct, because it does not take into account the total discount of the order.

 

Thanks for your help!!

Anonymous
Not applicable

@ContabilidadBI,

 

I am sorry, but I can not understand your problem. Could you show some simple demo data? I can use my examples SalesHeader and SalesDetail. And create manually the final Sales table?

Hi @Anonymous,

 

This is Sales Header:

SalesIDTotalDiscountTotalAmountDiscountpct
1106000,016666667
22040000,005
33090000,003333333
4401010000,00039604

 

This is Sales Detail:

SalesIDUnitPriceQuantityTotalAmount
11001100
15010500
2200204000
3300309000
44004016000
45005025000
4600100

60000

 

 

And this would be the Sales table:

SalesIDUnitPriceQuantityTotalAmountTotalDiscountDiscountpctLineDiscount
11001100100,0166666671,666666667
15010500100,0166666678,333333333
2200204000200,00520
3300309000300,00333333330
44004016000400,000396046,336633663
45005025000400,000396049,900990099
460010060000400,00039604

23,76237624

 

 

I hope you can understand me better with this. What I need to do is to allocate the total discount of the order, at the individual row level (using the same % for every line of the same order), so I can have all the data in only one fact table. For example the first order has a total discount of 10 $, and it has 2 rows in the sales detail table, so the line discount of the two rows should amount to 10 $. The second order has only one row, thats why the line discount is the same as the total discount for that order.

 

Thanks for your help!!

Anonymous
Not applicable

Hello @ContabilidadBI,

 

Now I understood what you need. This I what I got:

Sales TableSales Table

 These are the originals tables:SalesHeaderSalesHeader

 

SalesDetailSalesDetail

  

And this is the explanation, step by step:

1 - Go to SalesDetail and create a new Amount column:New amount column on SalesDetailNew amount column on SalesDetail

Table.AddColumn(#"Changed Type", "Amount", each [UnitPrice] * [Quantity])

 2 - To get the total amount by saleID, duplicate the SalesHeader table, merge it with SalesDetail and sum the column AmountThe table TotalAmountThe table TotalAmount

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0UIrViVYyAjKNIExjINMYwjQBMk2AzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SalesID = _t, TotalDiscount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesID", Int64.Type}, {"TotalDiscount", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"SalesID"},SalesDetail,{"SalesID"},"SalesDetail",JoinKind.Inner),
    #"Expanded SalesDetail" = Table.ExpandTableColumn(#"Merged Queries", "SalesDetail", {"Amount"}, {"Amount"}),
    #"Grouped Rows" = Table.Group(#"Expanded SalesDetail", {"SalesID"}, {{"TotalAmount", each List.Sum([Amount]), type number}})
in
    #"Grouped Rows"

 

 

 

 

 

3 - Merge SalesHeader with TotalAmount table and add a custom column named DiscountPct to it:

SalesHeader table with two new columns - TotalAmount and DiscountPctSalesHeader table with two new columns - TotalAmount and DiscountPct

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0UIrViVYyAjKNIExjINMYwjQBMk2AzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SalesID = _t, TotalDiscount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesID", Int64.Type}, {"TotalDiscount", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"SalesID"},TotalAmount,{"SalesID"},"TotalAmount",JoinKind.Inner),
    #"Expanded TotalAmount" = Table.ExpandTableColumn(#"Merged Queries", "TotalAmount", {"TotalAmount"}, {"TotalAmount"}),
    #"Added Custom" = Table.AddColumn(#"Expanded TotalAmount", "DiscountPct", each [TotalDiscount] / [TotalAmount])
in
    #"Added Custom"

 

 

 

 

4 - And finally, merge SalesDetail with SalesHeader as a new table named Sales and add a custom column named LineDiscount:The Sales TableThe Sales Table

 

 

 

let
    Source = Table.NestedJoin(SalesDetail,{"SalesID"},SalesHeader,{"SalesID"},"SalesHeader",JoinKind.Inner),
    #"Expanded SalesHeader" = Table.ExpandTableColumn(Source, "SalesHeader", {"TotalDiscount", "DiscountPct"}, {"TotalDiscount", "DiscountPct"}),
    #"Added Custom" = Table.AddColumn(#"Expanded SalesHeader", "LineDiscount", each [DiscountPct] * [Amount])
in
    #"Added Custom"

 

Hope it helps!

 

Thanks @Anonymous,

 

That is a perfect solution.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.