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
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).
Community ChampionYour 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 ResultHi tamerj1
Thank you so much for all yout input!
I think we're almost there, but something is still up with the last UNIONs.
I keep getting the "All table arguments of the function UNION should have the same amount of columns." message.VAR NotSplittedOrders = ADDCOLUMNS ( FILTER ( 'Order Header', NOT ( 'Order Header'[Order No] IN VALUES ( 'Splitted orders 1'[Order No] ) ) ), "Percentage", 100 )
If I understood the code correct it filters my headers on all order lines that are not present in the splitted order lines and afterwards merges them. So the union above would result in a far larger table than the union below.VAR SplittedOrders = UNION ( Owner1Table, Owner2Table )
As in table 'Splitted orders 1' I only have 3 columns wheras in 'Order header' there are about 50 or more.So I'd need to join my Splitted headers over the existing values in the Order headers too. So my splitted orders get enriched with the missing data.
Or have to extract the three columns from the order header and put this table inbetween my original header with a one to many and then the new header with a many to many to the lines. But this looks a little cumbersome to me.