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
To create the new table
Splitted orders 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 Result =
UNION ( Owner1Table, Owner2Table )
RETURN
ResultI would appreciate if you clearify furtehr your other requirements to help you with.
Thank you tamerj1
This code already worked like a charm to get the table in a different format! Thanks.
Now my biggest challenge is to use this table to split my actual orders & sales without slowing down the system. I have my 'order headers' which contain my customer name, owner of the order, adress fields etc. and my order lines which define the products on the order.
So if in my split order table it says that ORDER1 only belongs for 50% to OWNER1 and 50% to OWNER2 I need to take that into account in every calculation / visual I make. That I only need 50% of the quantity for that sales orderline when displaying info to OWNER1 and 50% when I'm displaying info to OWNER2.
The leading manager of OWNER1 and OWNER2 obviously needs to see the grand total and how much of that total belonged to OWNER1 and how much to OWNER2. To do so I'm using roles where the owner sees the full data set and OWNER1 and OWNER2 only see the data from the orders they own.Below you can see what I thought of and tried so far to get my data to appear:
I tought I'd need a full join between headers & lines and then join this table again to my split orders. So every order that is splitted appears twice; once with the initial owner an its amount according to the percentage; and once with the second owner and its percent according to the percentage. That way I would keep having the correct total if I filtered on order number, or on owner.
Only when I tried making the joins (through the guided setup) the immense amount of data slowed everything down terribly.
So I tried using only one join between the header and the split orders and then use calculated fieds / columns and lookups to get the correct amount for the ower based on the percentage I shared in the header. But none of those options gave me the desired totals, either it was incorrect when filtering on order level or on owner level.
Tried now through UNION with the new table and I got issues with the amount of columns, as I cannot use a calculated table in PowerQuery where I was making my joins to get a new table.- tamerj1
Hi 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 percentageIn 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).
