How to duplicate rows whilst splitting sales value in pre-determinded rates

[Anonymous]

Hi all,

I have a problem regarding combination products. The situation is as follows:

- We sell combination tickets

- We own the bus tours, thus all of these sales are allocated to ourselves

- The other product is 3rd party and we have to allocate their part off the sales to them

- The 3rd party gets a standard amount (pre-determined)

- We allocate the rest of the sales to ourselves (to bus in this case)

- We want to duplicate the rows because of the quantity 1 combination ticket = 1 bus ticket & 1 other

The original situation can be find below in the first table.

Let's assume that in the second and third transaction the customer got a discount. However, the 3rd party gets their standard price and we allocate the rest to ourselves, which means that our sales per bus ticket vary.

Can someone explain How I can get from situation 1 to 2? (Preferably in DAX without M)

Thank you!

1

2

[ChrisMendoza]

@Anonymous - 

A couple of questions:

1. Were you able to do this in some other program before? Maybe Excel or something?
2. Why would limit your options by not including Power Query? Data transformation is pretty much what you're asking for here.
3. Your final result is a table? Or do you just need 'that number'
4. How are the [Sales] values created? For example why does a bus have a value of 30 in one transaction but a value of 25 in another and 20 in yet another?

Just to name a few.

[Anonymous]

Hi Chris,

Thanks for your response!

1. We want to specifically do this in power bi because we'll export new data to power bi every week, thus we want to automate this process instead of doing this manually every time for six different databases.

2. I know that this is the case, but its just that my M-language skills are very limited. If there is an option to do this with dax I would prefer that over the other, but its not that the options are limited. Any solution is more than welcome.

3. The table

4. The allocation from the bus prices depends on the total price of the combination ticket. If we grant the customers a discount (vouchers, discounted limited combination tickets), we'll still have to allocate the standard rate to the 3rd party. This means that the leftovers of the transaction is allocated to the bus product.

I hope this made it a litte bit more clear to you.

[ChrisMendoza]

@Anonymous -

I am still unsure about how the [Sales] are calculated however I have the start of a possible solution for you using the Power Query Editor. Maybe this will 'spark' a thought in you or someone else.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLPy8nMSwUynEqLFWJKDQyMzBSi8vOBAiBZQwMDpVidaCUjHCqdgSK5iUAxkAILiFpjIDM4I78AVaVTfmIJ1FBLoLpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TransactionKey = _t, #"Sales Channel" = _t, Product = _t, Quantity = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TransactionKey", Int64.Type}, {"Sales Channel", type text}, {"Product", type text}, {"Quantity", Int64.Type}, {"Sales", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Product", Splitter.SplitTextByDelimiter("&", QuoteStyle.Csv), {"Product.1", "Product.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Product.1", type text}, {"Product.2", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Product.2", Text.Trim, type text}}),
    P1 = Table.RemoveColumns(#"Trimmed Text",{"Product.2"}),
    P2 = Table.RemoveColumns(#"Trimmed Text",{"Product.1"}),
    #"Combine P1P2" = Table.Combine({#"P1",#"P2"}),
    #"Merged Columns" = Table.CombineColumns(#"Combine P1P2",{"Product.1", "Product.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Product"),
    #"Sorted Rows" = Table.Sort(#"Merged Columns",{{"TransactionKey", Order.Ascending}})
in
    #"Sorted Rows"

Should produce a table as below:

[Anonymous]

Do you think that this is even possible in M or not?

[ChrisMendoza]

@Anonymous -

Sure, why wouldn't it be possible? It may be a combination of Power Query and DAX that solves the problem. A problem that I still see as 'the way your data is stored' or my lack of understanding possibly.

So, your column [Sales] is not actually 'Total Sales'? In your sample, for transaction 2, '80' is not the [Total Sales]; it should be ( 2 * 80 ) = 160 correct? The reason I say this is because you state that the standard rate for Cinema is '55' and that should be multiplied by Quantity 2 which is '110' and that is > than [Sales] = '80'. I am guessing you're not in business to lose money.

[Anonymous]

You're correct! It should indeed be 160, I made a mistake in the example, then it would represent Total Sales.

[ChrisMendoza · Accepted solution]

@Anonymous - 

I realize the numbers/math isn't 100% accurate, still pending my last message response, however the below should be able to serve as a model for you to follow to accomplish your original post.

Add this to create a table 'Products' - These records are your 'Known Costs' (i.e. Standard that you mentioned)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPLFHSUTI3UIrViVZyzsxLzU0E8k1Nwfyo/HyoZCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Cost = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Cost", Int64.Type}})
in
    #"Changed Type"

Modifiy the table 'Transactions' to be

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLPy8nMSwUynEqLFWJKDQyMzBSi8vOBAiBZQwMDpVidaCUjHCqdgSK5iUAxkAILiFpjIDM4I78AVaVTfmIJ1FBLoLpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TransactionKey = _t, #"Sales Channel" = _t, Product = _t, Quantity = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TransactionKey", Int64.Type}, {"Sales Channel", type text}, {"Product", type text}, {"Quantity", Int64.Type}, {"Sales", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Product", Splitter.SplitTextByDelimiter("&", QuoteStyle.Csv), {"Product.1", "Product.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Product.1", type text}, {"Product.2", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Product.2", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "knownCost", each (let pProduct = [Product.2] in Table.SelectRows(Products, each [Product] = pProduct)){0}[Cost]),
    P1 = Table.RemoveColumns(#"Added Custom",{"Product.2"}),
    P2 = Table.RemoveColumns(#"Added Custom",{"Product.1"}),
    #"Combine P1P2" = Table.Combine({#"P1",#"P2"}),
    #"Merged Columns" = Table.CombineColumns(#"Combine P1P2",{"Product.1", "Product.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Product"),
    #"Sorted Rows" = Table.Sort(#"Merged Columns",{{"TransactionKey", Order.Ascending}})
in
    #"Sorted Rows"

*** #"Added Custom" step is the only addition to the code; gets the 'Known Costs' before creating an additional record for the same transaction; Should look like the below image:

Final Power Query Table 'Transactions' after all steps should look like:

If not automagically created, create the relationship between 'Transactions' & 'Products' in Relationships (Power BI - DAX proper)

Add Calculated Column [Allocated Cost] in Power BI - DAX:

Allocated Cost = 
IF(
    RELATED(Products[Cost]),
    RELATED(Products[Cost]),
    Transactions[Sales] - Transactions[knownCost]
)

[Anonymous]

Thanks for the effort! I will try to implement this in my actual sheet and will let you know if it worked.

[Anonymous]

First of all thanks for responding!

The sales are calculated as follows (keeping it simple):

standard prices for

- zoo 70

- cinema 55

- boat 70

sales for bus = [Sales] - [X-price * Quantity] 

Let X be the price of one of the three products above.

Example 1:

Product = One combination ticket Bus & Zoo

Sales = 110

Quantity =1 

sales for bus = [Sales] - [X-price * Quantity] 

where X is the price of the Zoo in this case

This leads to the following two transactions.

Product = Ticket Zoo

Sales = 70

Quantity =1 

Product = Ticket Bus

Sales = 110 - [70*1] = 40

Quantity =1 

Example 2:

Same example but lets assume that the customer gets a discount of 20.

Product = One combination ticket Bus & Zoo

Sales = 90

Quantity =1 

This leads to the following two transactions.

Product = Ticket Zoo

Sales = 70

Quantity =1 

Product = Ticket Bus

Sales = 90 - [70*1] = 20

Quantity =1 

Conclusion: Sales that are allocated to Bus (own product) depend on the total amount of sales, while sales for the 3rd party products will always remain the same