Forum Discussion
Splitting data out over a predefined percentage and using that data for new calculations
KatrienVds
No need to select all columns. We will follow your strategy. Use this code to generate the appended header table.Order Header 2 = VAR Owner1Table = SELECTCOLUMNS ( 'Splitted orders 1', "@Order No", 'Splitted orders 1'[Order No], "@Owner", 'Splitted orders 1'[Owner 1], "@Percentage", 'Splitted orders 1'[Percentage] ) VAR Owner2Table = SELECTCOLUMNS ( 'Splitted orders 1', "@Order No", 'Splitted orders 1'[Order No], "@Owner", 'Splitted orders 1'[Owner 2], "@Percentage", 1 - 'Splitted orders 1'[Percentage] ) VAR SplittedOrders = UNION ( Owner1Table, Owner2Table ) VAR OrderHeader = SELECTCOLUMNS ( 'Order Header', "Order No", 'Order Header'[Order No], "Owner", 'Order Header'[Owner] ) VAR NotSplittedOrders = ADDCOLUMNS ( FILTER ( OrderHeader, NOT ( [Order No] IN VALUES ( 'Splitted orders 1'[Order No] ) ) ), "Percentage", 1 ) VAR Result = UNION ( NotSplittedOrders, SplittedOrders ) RETURN ResultThen make tthis table in between the two tables exactly as you have suggested
Community ChampionHi KatrienVds
I don't believe you need to make a full join. I agree that header-details data model is not the best option but neither is joining everything in one table.
I'm not sure about your required visual(s) but you can (In PowerBi not In PowerPivot) create Many-Many relationship between 'Split Orders' and 'Order Lines'. You can then slice data by Owner.
Can you provide any sketch that mimics what kind of visual you're up to?
Hi tamerj1
The split orders are only a fraction of the orders. So I don't think a Many-Many will work.
As OWNER1 would have to see the total of all his orders, lets say:
OWNER1 has 500 orders on his name in Header table and has 150 orders on the split order table.
- 50 are orders on his name in the header are shared with someones & thus reappear twice in split orders: once on his name and once on the other owner with the percentage
- 100 of whom are owned by another user but to which he also owns a part and thus also appear in the split with the percentage
In the report OWNER1 should see his total sales, meaning the sum of the lines from the 450 orders which are 100% his. Added up to the 450 sum, it would have to split the lines of the 50 orders on his name but only partially his, over the percentage that is his (which is in the split orders) AND the 100 orders not on his name but where he also gets a part from.
So the overlap of the split orders and the headers is always causing me difficulties, hence why I wanted to join them into one table.
We have many visuals, pie charts, tables, regular charts,... but they're all pretty basic, based on the quantity and amount on the order lines and sliced by the date, type of product and or the user that owns it.
Which in the current case is not taking the shared orders into account anymore. It is just slicing my totals in full over the owners. Meaning they often have too much on their name or too little.
OWNER1 would have 500 orders in full on his name and have 'too' much from those 50 he shares with someone else; and 'too little' from those other 100 that he is supposed to get from another order.
I don't want to display the difference in a separate table I just want one amount per user that I can slice. Which sketch can I provide?
Ofcourse there is a lot more complexity to it as I have other tables in the model that also work on those order numbers, invoices, shipments etc. but trying to tackle one issue at a time. As the solution will be similar or quicker for the other tables (I hope).
- tamerj1
Your description is amazing!
I believe you've almost achieved it. We just need to make sure that the full joined table between header and split is the correct one then we can create many-many relationship between the full joint table and the lines table. The rest is simple. Can you please write the names of the columns you need to keep from each table? Thanks and have a great day!
Hi tamerj1
I really hope so as I've tried plenty things before so far without succes.
I need all columns from my header table (which are actually quite a few to write down).
Isn't there some sort of get all like there is in SQL?
From the second split table (to use the names from my example) :
Order No = Order No on the header (so the split ones will be double)Owner = Owner on the order header
Percentage is the only "extra" column that would appear to the header
- tamerj1
Hi KatrienVds
The following code shall produce the correct header table. You can create a many-many relationship between 'Order Header 2'[Order No] and 'Order Line'[Order No]. Having the Percentage column, it shall be easy to retrieve the correct percentage of each project/owner combination either 100% or splitted percentageOrder Header 2 = VAR Owner1Table = SELECTCOLUMNS ( 'Splitted orders 1', "@Order No", 'Splitted orders 1'[Order No], "@Owner", 'Splitted orders 1'[Owner 1], "@Percentage", 'Splitted orders 1'[Percentage] ) VAR Owner2Table = SELECTCOLUMNS ( 'Splitted orders 1', "@Order No", 'Splitted orders 1'[Order No], "@Owner", 'Splitted orders 1'[Owner 2], "@Percentage", 100 - 'Splitted orders 1'[Percentage] ) VAR SplittedOrders = UNION ( Owner1Table, Owner2Table ) VAR NotSplittedOrders = ADDCOLUMNS ( FILTER ( 'Order Header', NOT ( 'Order Header'[Order No] IN VALUES ( 'Splitted orders 1'[Order No] ) ) ), "Percentage", 100 ) VAR Result = UNION ( NotSplittedOrders, SplittedOrders ) RETURN Result