cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

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

 

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

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

 

aitormadoz Frequent Visitor
Frequent Visitor

Re: Create a price list comparison table

Thanks a lot

 

Simple and effective solution

 

Yours,

 

Aitor

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 52 members 1,060 guests
Please welcome our newest community members: