Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Hello @ContabilidadBI,
Now I understood what you need. This I what I got:
These are the originals tables:
And this is the explanation, step by step:
1 - Go to SalesDetail and create a new Amount column:
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 Amount
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:
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:
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!
Hello @ContabilidadBI,
This is your data:
This is the final result using Power Query:
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
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!
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!!
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:
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!!
Hello @ContabilidadBI,
Now I understood what you need. This I what I got:
These are the originals tables:
And this is the explanation, step by step:
1 - Go to SalesDetail and create a new Amount column:
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 Amount
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:
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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |