Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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] )
Proud to be a Super User!
@Anonymous -
A couple of questions:
Just to name a few.
Proud to be a Super User!
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.
@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:
Proud to be a Super User!
Do you think that this is even possible in M or not?
@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.
Proud to be a Super User!
You're correct! It should indeed be 160, I made a mistake in the example, then it would represent Total Sales.
@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] )
Proud to be a Super User!
Thanks for the effort! I will try to implement this in my actual sheet and will let you know if it worked.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |