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:
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.
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.
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.