cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aitormadoz Frequent Visitor
Frequent Visitor

Create a price list comparison table

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:

 

SKUPriceListPrice
AX12
AY13
AZ11
AT10,5
BX6
BY5
BZ4,5
BT7

 

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/ TOXYZT
 X0-111,5
 Y1022,5
 Z-1-200,5
 T-1,5-2,5-0,50

 

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Create a price list comparison table

@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"

 

createon.png

View solution in original post

fenchurchio Regular Visitor
Regular Visitor

Re: Create a price list comparison table

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.

 

2019-07-22_14h00_13.png

 

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:

2019-07-22_14h02_38.png

 

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Create a price list comparison table

@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"

 

createon.png

View solution in original post

fenchurchio Regular Visitor
Regular Visitor

Re: Create a price list comparison table

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.

 

2019-07-22_14h00_13.png

 

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:

2019-07-22_14h02_38.png

 

View solution in original post

aitormadoz Frequent Visitor
Frequent Visitor

Re: Create a price list comparison table

Thanks a lot

 

Simple and effective solution

 

Yours,

 

Aitor

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 303 members 2,781 guests
Please welcome our newest community members: