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

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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

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

    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'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.