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

[ContabilidadBI]

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!

[Anonymous]

Hello @ContabilidadBI,

This is your data:

SalesHeader

SalesDetail

This is the final result using Power Query:

Sales

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!

[ContabilidadBI]

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

This is Sales detailThis 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]

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

SalesHeader

SalesDetail

Sales

[ContabilidadBI]

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]

@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?

[ContabilidadBI]

Hi @Anonymous,

This is Sales Header:

SalesID	TotalDiscount	TotalAmount	Discountpct
1	10	600	0,016666667
2	20	4000	0,005
3	30	9000	0,003333333
4	40	101000	0,00039604

This is Sales Detail:

SalesID	UnitPrice	Quantity	TotalAmount
1	100	1	100
1	50	10	500
2	200	20	4000
3	300	30	9000
4	400	40	16000
4	500	50	25000
4	600	100	60000

And this would be the Sales table:

SalesID	UnitPrice	Quantity	TotalAmount	TotalDiscount	Discountpct	LineDiscount
1	100	1	100	10	0,016666667	1,666666667
1	50	10	500	10	0,016666667	8,333333333
2	200	20	4000	20	0,005	20
3	300	30	9000	30	0,003333333	30
4	400	40	16000	40	0,00039604	6,336633663
4	500	50	25000	40	0,00039604	9,900990099
4	600	100	60000	40	0,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 · Accepted solution]

Hello @ContabilidadBI,

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

Sales Table

 These are the originals tables:SalesHeader

SalesDetail

And this is the explanation, step by step:

1 - Go to SalesDetail and create a new Amount column:New 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 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 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 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!

[ContabilidadBI]

Thanks @Anonymous,

That is a perfect solution.