cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

Accepted Solutions
caaarlos Member
Member

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

Hello @ContabilidadBI,

 

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

image.pngSales Table

 These are the originals tables:image.pngSalesHeader

 

image.pngSalesDetail

  

And this is the explanation, step by step:

1 - Go to SalesDetail and create a new Amount column:image.pngNew 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 Amountimage.pngThe 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:

image.pngSalesHeader 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:image.pngThe 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!

 

8 REPLIES 8
caaarlos Member
Member

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

Hello @ContabilidadBI,

  

This is your data:

image.pngSalesHeader

 

image.pngSalesDetail

 

This is the final result using Power Query:

 

image.pngSales

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!

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

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

 

Detail.PNGThis is Sales detailHeader.PNGThis 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!

caaarlos Member
Member

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

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

 

image.pngSalesHeader

 

image.pngSalesDetail

 

image.pngSales

 

 

 

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

Hi @caaarlos,

 

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!!

caaarlos Member
Member

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

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

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

Hi @caaarlos,

 

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!!

caaarlos Member
Member

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

Hello @ContabilidadBI,

 

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

image.pngSales Table

 These are the originals tables:image.pngSalesHeader

 

image.pngSalesDetail

  

And this is the explanation, step by step:

1 - Go to SalesDetail and create a new Amount column:image.pngNew 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 Amountimage.pngThe 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:

image.pngSalesHeader 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:image.pngThe 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!

 

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

Thanks @caaarlos,

 

That is a perfect solution.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 210 members 1,667 guests
Please welcome our newest community members: