Forum Discussion
Splitting data out over a predefined percentage and using that data for new calculations
Hi BI Community
I'm having difficulties to visualize an important feature in our reports. I used to have the data in a fully different format, in the new environment I'm unable to do SQL queries on my data so I'm bound to Power BI solutions.
Part of my issue already begins at my splitted orders table.
Splitted orders 1
Order No | Owner 1 | Owner 2 | Percentage |
ORDER1 | Person1 | Person2 | 50 |
ORDER3 | Person3 | Person1 | 75 |
I manually made Splitted orders 2 out of the data from splitted orders 1 as I wasn't able to have any code do the trick in Power BI itself. Splitted orders 1 is actually a dynamic table that's getting data from our employees, they can add but also modify/delete. Is there a way I can get DAX or Power Query to give me the values as in splitted orders 2 or is there no other option than to rebuild the system to which they enter their data? Or perhaps my issue is't beginning here like I thought it was and I can keep splitted orders 1.
Splitted orders 2
Order No | Owner | Percentage |
ORDER1 | Person1 | 50 |
ORDER1 | Person2 | 50 |
ORDER3 | Person3 | 75 |
ORDER3 | Person1 | 25 |
Situation:
We have sales orders, they are being split out in a header table and a lines table.
Every sales order is property of one owner.
Order Header
Order No | Owner |
ORDER1 | Person1 |
ORDER2 | Person2 |
ORDER3 | Person3 |
In special cases an order can get 'splitted', meaning that the total amount of the order gets devided over a specific percentage and two owners. (see the issue on the top about splitted orders, here I used the splitted order 2)
Splitted orders
Order No | Owner | Percentage |
ORDER1 | Person1 | 50% |
ORDER1 | Person2 | 50% |
ORDER3 | Person3 | 75% |
ORDER3 | Person1 | 25% |
I thought that making a full join on the splitted orders and the headers, along with the new headers and the lines would help me fix the issue. Yet this doesn't seem like a best practice to me as this is making the entire report extremely slow. So I continued to look for a different solution.
I kept the full joined headers and calculated the lines based on the percentage of the splitted order.
But I'm unable to get my sums to work that way. As I cannot calculate the field without making a sum() of all values.
Order No | Owner | Percentage |
ORDER1 | Person1 | 50% |
ORDER1 | Person2 | 50% |
ORDER2 | Person2 | 100% |
ORDER3 | Person3 | 75% |
ORDER3 | Person1 | 25% |
Meaning I made a calculated field AmountSplit = sum([Amount]) * sum('SplitHeader'[Percentage])
This works for me on the order level but obviously I need to keep the correct sums if I start combining my orders.
Order No | Product | Amount |
ORDER1 | Product1 | 10 |
ORDER1 | Product2 | 20 |
ORDER2 | Product1 | 10 |
ORDER2 | Product2 | 10 |
ORDER2 | Product3 | 10 |
ORDER2 | Product4 | 10 |
ORDER3 | Product1 | 20 |
ORDER3 | Product2 | 10 |
ORDER3 | Product3 | 20 |
If I would want to show the total amounts per person I'd need to get the AmountSplit1 result un-summarized. The DAX code makes sure I'm having AmountSplit2. I'm unable to remove the SUM part as it's no longer calculating after that.
Owner | Amount | AmountSplit1 | AmountSplit2 |
Person 1 | 30 | 27,5 | 60 |
Person 2 | 40 | 55 | 105 |
Person 3 | 50 | 37,5 | 37,5 |
Also tried making a calculated column in my lines both using SUM and no SUM.
In the example I summarised the tables with only the basic data that has to be recalculated but there are a bunch of other fields in both header and lines that I need for calculations.
Anyone who might have the magical solution?
Thanks!
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
14 Replies
- tamerj1
Community Champion
Hi KatrienVds
To create the new tableSplitted 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?
