Forum Discussion

KatrienVds's avatar
KatrienVds
Frequent Visitor
4 years ago
Solved

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 ...
database
DAX
DAX Calculation
percentage
Power BI Desktop
Power Query
  • tamerj1's avatar
    tamerj1
    4 years ago

    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's avatar
KatrienVds
Frequent Visitor
4 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
    KatrienVds
    Frequent Visitor
    4 years ago

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