Create a price list comparison table

[aitormadoz]

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

[Zubair_Muhammad · Accepted solution]

@aitormadoz 

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"

[Anonymous · Accepted solution]

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:

[aitormadoz]

Thanks a lot

Simple and effective solution

Yours,

Aitor

[Zubair_Muhammad · Accepted solution]

@aitormadoz 

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"