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 am trying to create a comparison table where I can compare differences between prices for a single reference among several price lists.
Data structure is as follows:
SKU | PriceList | Price |
A | X | 12 |
A | Y | 13 |
A | Z | 11 |
A | T | 10,5 |
B | X | 6 |
B | Y | 5 |
B | Z | 4,5 |
B | T | 7 |
The result I want to get is a table looking like this:
For SKU A (selected in a slicer of by clicking in another table)
sku: | A | ||||
FROM/ TO | X | Y | Z | T | |
X | 0 | -1 | 1 | 1,5 | |
Y | 1 | 0 | 2 | 2,5 | |
Z | -1 | -2 | 0 | 0,5 | |
T | -1,5 | -2,5 | -0,5 | 0 |
Could anyone please give a clue on how to perform this calculation / adapt data structure
mainly is calculating price difference among all suitable combinations for each SKU and price list
thanks in advance
Solved! Go to Solution.
One way could be to CROSS JOIN price list of each SKU with itself and then computing the difference
Check this Power Query Solution. Please see attached file's Query Editor for steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoAYkMjpVgdCDcSxDWGc6NAXEM4NwTENdAzBQs4QXWbwXkgzQg5kF4TJLUgzeZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SKU = _t, PriceList = _t, Price = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"PriceList", type text}, {"Price", type number}}), #"Added Custom" = Table.AddColumn(ChangedType, "Comparison", each Table.SelectRows(ChangedType, (X)=>X[SKU] =[SKU])), #"Expanded Comparison" = Table.ExpandTableColumn(#"Added Custom", "Comparison", {"PriceList", "Price"}, {"Comparison.PriceList", "Comparison.Price"}), #"Added Custom1" = Table.AddColumn(#"Expanded Comparison", "Diff", each [Price]-[Comparison.Price]) in #"Added Custom1"
Here is an approach that will not require you to change the structure of your fact table. You just need another table in your model to represent the comparison price list and then you can let DAX calculations do the rest.
With a model like the above, you can use the following calculation, using the recently added TREATAS function.
I have created a sample workbook here, that you can download: Download
Find out more about the TREATAS function here: https://docs.microsoft.com/en-us/dax/treatas-function
Comparison = VAR varComparisonPrice = CALCULATE ( MAX ( [Price] ), TREATAS ( VALUES ( 'Comparison Price List'[Comparison Price List] ), 'Price List'[Price List] ) ) VAR varPrice = CALCULATE ( MAX ( [Price] ) ) RETURN varComparisonPrice - varPrice
You will get the result that you expect:
Here is an approach that will not require you to change the structure of your fact table. You just need another table in your model to represent the comparison price list and then you can let DAX calculations do the rest.
With a model like the above, you can use the following calculation, using the recently added TREATAS function.
I have created a sample workbook here, that you can download: Download
Find out more about the TREATAS function here: https://docs.microsoft.com/en-us/dax/treatas-function
Comparison = VAR varComparisonPrice = CALCULATE ( MAX ( [Price] ), TREATAS ( VALUES ( 'Comparison Price List'[Comparison Price List] ), 'Price List'[Price List] ) ) VAR varPrice = CALCULATE ( MAX ( [Price] ) ) RETURN varComparisonPrice - varPrice
You will get the result that you expect:
Thanks a lot
Simple and effective solution
Yours,
Aitor
One way could be to CROSS JOIN price list of each SKU with itself and then computing the difference
Check this Power Query Solution. Please see attached file's Query Editor for steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoAYkMjpVgdCDcSxDWGc6NAXEM4NwTENdAzBQs4QXWbwXkgzQg5kF4TJLUgzeZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SKU = _t, PriceList = _t, Price = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"PriceList", type text}, {"Price", type number}}), #"Added Custom" = Table.AddColumn(ChangedType, "Comparison", each Table.SelectRows(ChangedType, (X)=>X[SKU] =[SKU])), #"Expanded Comparison" = Table.ExpandTableColumn(#"Added Custom", "Comparison", {"PriceList", "Price"}, {"Comparison.PriceList", "Comparison.Price"}), #"Added Custom1" = Table.AddColumn(#"Expanded Comparison", "Diff", each [Price]-[Comparison.Price]) in #"Added Custom1"
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 |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |