Splitting data out over a predefined percentage and using that data for new calculations

[KatrienVds]

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!

[tamerj1]

Hi @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
    Result

I would appreciate if you clearify furtehr your other requirements to help you with.

[KatrienVds]

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?

[KatrienVds]

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]

@KatrienVds 

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!

[KatrienVds]

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 percentage

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", 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

[KatrienVds]

Hi @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. 

[tamerj1]

@KatrienVds 
"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. "
Yes great idea! that would be the best option in my openion.

[KatrienVds]

About to give it a try like this.
Managed to get the summarized table inbetween. Fingers crossed.  

[tamerj1]

Hi @KatrienVds 
Regarding the error, yes you should have the same number of columns, this is why I asked you what columns you have and what columns you want to keep. You can share ascreenshot to help you further.

Regarding the code, yes you are right. The idea is to filter the header table removing the order that exist in the splitted table then append the filtered table with the splitted one. This way we get the complete data in one table.
Here is a sample file for your reference. I went further with caclulations to explain to you next steps. https://www.dropbox.com/t/Y24cbKsw85In4BMV

[KatrienVds]

Hi @tamerj1 

I will have to keep almost all of the columns from my order table, that's why I was asking if there isn't some sort of select all function like in SQL. 

On a different note, 
I think a normal append won't help because in the Splitted orders we only get 3 values.
All other info such as sell-to company, bill-to company, delivery terms, payment terms, delivery location (street, postcode, city, country), order date, due date, etc are all being stored in the header. To have an idea of what all gets stored in the header: https://dynamicsdocs.com/nav/2017/w1/table/sales-header 

If I'd have to add all lets say columns manually to the other table they'll result in NULL values and I'd have to do a lookup for each in my original table? Which in this case again looks quite cumbersome for something SQL would fix with a JOIN operation.  

So either I'll have to make a mini table and add it in there or I'd have to find way to use working JOIN operations instead of UNIONS.

[tamerj1 · Accepted solution]

@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
    Result

Then make tthis table in between the two tables exactly as you have suggested

[KatrienVds]

Been playing with this solution for a few days now and so far it seems to work. 
Thank you 😊