Price Variance by Category & Year

[zileng]

Hello,

I need to calculate price variance of duplicate items (eg: Item A, B, and C):

• By Primary Color,  Secondary Color, and Year
• And use Max Value only

Primary Color	Secondary Color	Item	Year	Price
Blue	Light Blue	A	2019	11
Blue	Light Blue	A	2020	13
Blue	Dark Blue	B	2018	200
Blue	Dark Blue	B	2019	201
Red	Light Red	C	2019	55
Red	Light Red	C	2020	50
Red	Light Red	C	2020	61
Grey	Grey	D	2019	1
Orange	Orange	E	2020	40
While	While	F	2018	90

Result:

Primary Color	Secondary Color	Item	2018	2019	2020	2018 vs 2019	2019 vs 2020
Blue	Light Blue	A		11	13		18.2%
Blue	Dark Blue	B	200	201		0.5%
Red	Light Red	C		55	61		10.9%

Thank you.

Regards,

Zi

[Mariusz · Accepted solution]

Hi @zileng 

Please see the attached file with a DAX solution, this can be done in Power Query but it's better to calculate it using DAX.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[dax · Accepted solution]

Hi @zileng ,

You could refer to @Mariusz 's suggestions or try below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUfLJTM8oUYByHIHYyMDQEkgZGirF6uBTZWQAUmWMrMolsSgbpsgJYpQFmDIgoMoSQoFVBaWmwC2EsJ0RikxN8aoBu8nUgLAaM4hd7kWplUAelHJB8j1Y2r8oMS8d5Ew4wxVhhAnEmvCMzByQDIx2Q/jbEqgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Primary Color" = _t, #"Secondary Color" = _t, Item = _t, Year = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Primary Color", type text}, {"Secondary Color", type text}, {"Item", type text}, {"Year", Int64.Type}, {"Price", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US")[Year]), "Year", "Price", List.Max),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "2018 vs 2019", each [2018]/[2019]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "2019vs 20202", each [2019]/[2020]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Item] = "A" or [Item] = "B" or [Item] = "C"))
in
    #"Filtered Rows"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Smauro · Accepted solution]

I had some fun doing it dynamically in a query, but as @Mariusz said, it's easier in dax.

Here it is:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUfLJTM8oUYByHIHYyMDQEkgZGirF6uBTZWQAUmWMrMolsSgbpsgJYpQFmDIgoMoSQoFVBaWmwC2EsJ0RikxN8aoBu8nUgLAaM4hd7kWplUAelHJB8j1Y2r8oMS8d5Ew4wxVhhAnEmvCMzByQDIx2Q/jbEqgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Primary Color" = _t, #"Secondary Color" = _t, Item = _t, Year = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Primary Color", type text}, {"Secondary Color", type text}, {"Item", type text}, {"Year", Int64.Type}, {"Price", Int64.Type}}),
    
    #"Max Price per Year" = Table.Group(#"Changed Type", {"Primary Color", "Secondary Color", "Item", "Year"}, {{"Price", each List.Max([Price]), type number}}),
    #"Find Comparable" = Table.Group(#"Max Price per Year", {"Primary Color", "Secondary Color", "Item"}, {{"c", each Table.RowCount(_), Int64.Type}, {"A", each _, type table [Primary Color=text, Secondary Color=text, Item=text, Year=text, Price=number]}}),
    #"Filtered Comparable" = Table.SelectRows(#"Find Comparable", each ([c] > 1)),
    #"Remove Count" = Table.RemoveColumns(#"Filtered Comparable",{"c"}),
    #"Expanded Year Price" = Table.ExpandTableColumn(#"Remove Count", "A", {"Year", "Price"}, {"Year", "Price"}),
    
    YearList = List.Sort(List.Distinct(Table.TransformColumnTypes(#"Expanded Year Price", {{"Year", type text}}, "en-GB")[Year])),
    #"Pivoted Years" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Year Price", {{"Year", type text}}, "en-GB"), YearList, "Year", "Price"),

    Comparisons = (l as list, n as number, optional out as text, optional pstep as text) =>
        let
            k = n - List.Count(l),
            newouts =
                if k= 0
                    then "let " & "Comp"&Text.From(k+1)&" = Table.AddColumn(#"&"""t"""&", "&""""&l{0}&" vs "&l{1}&""""&", each Value.Divide(["&l{1}&"], ["&l{0}&"])-1, Percentage.Type)"
                else
                    if k+1<n
                        then out & ",Comp"&Text.From(k+1)&" = Table.AddColumn(#"&""""&pstep&""""&", "&""""&l{0}&" vs "&l{1}&""""&", each Value.Divide(["&l{1}&"], ["&l{0}&"])-1, Percentage.Type)"
                    else out,
            Rest =
                if k+1<n
                    then @Comparisons(List.RemoveFirstN(l, 1), n, newouts, "Comp"&Text.From(k+1))
                else out & " in Comp"&Text.From(k)
        in Rest,
    RecBuild = (t as table) => [t = t, Table.AddColumn=Table.AddColumn, Value.Divide = Value.Divide, Percentage.Type = Percentage.Type],


    AddComparisons = Expression.Evaluate(Comparisons(YearList, List.Count(YearList)), RecBuild(#"Pivoted Years"))
in
    AddComparisons

Explanations:

From #"Max Price per Year" to #"Expanded Year Price" we get only data comparable, and the corresponding max for each year.

YearList helps us in Pivoting the years as columns.

Comparisons is a function that returns a query in text, with YearCount-1 steps, one for each comparison.

RecBuild is an easy function to build a record so we won't have to write a lot of things if we're calling it.

AddComparisons calls for the evaluation of Comparisons using RecBuild.

voila!

[Smauro · Accepted solution]

I had some fun doing it dynamically in a query, but as @Mariusz said, it's easier in dax.

Here it is:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUfLJTM8oUYByHIHYyMDQEkgZGirF6uBTZWQAUmWMrMolsSgbpsgJYpQFmDIgoMoSQoFVBaWmwC2EsJ0RikxN8aoBu8nUgLAaM4hd7kWplUAelHJB8j1Y2r8oMS8d5Ew4wxVhhAnEmvCMzByQDIx2Q/jbEqgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Primary Color" = _t, #"Secondary Color" = _t, Item = _t, Year = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Primary Color", type text}, {"Secondary Color", type text}, {"Item", type text}, {"Year", Int64.Type}, {"Price", Int64.Type}}),
    
    #"Max Price per Year" = Table.Group(#"Changed Type", {"Primary Color", "Secondary Color", "Item", "Year"}, {{"Price", each List.Max([Price]), type number}}),
    #"Find Comparable" = Table.Group(#"Max Price per Year", {"Primary Color", "Secondary Color", "Item"}, {{"c", each Table.RowCount(_), Int64.Type}, {"A", each _, type table [Primary Color=text, Secondary Color=text, Item=text, Year=text, Price=number]}}),
    #"Filtered Comparable" = Table.SelectRows(#"Find Comparable", each ([c] > 1)),
    #"Remove Count" = Table.RemoveColumns(#"Filtered Comparable",{"c"}),
    #"Expanded Year Price" = Table.ExpandTableColumn(#"Remove Count", "A", {"Year", "Price"}, {"Year", "Price"}),
    
    YearList = List.Sort(List.Distinct(Table.TransformColumnTypes(#"Expanded Year Price", {{"Year", type text}}, "en-GB")[Year])),
    #"Pivoted Years" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Year Price", {{"Year", type text}}, "en-GB"), YearList, "Year", "Price"),

    Comparisons = (l as list, n as number, optional out as text, optional pstep as text) =>
        let
            k = n - List.Count(l),
            newouts =
                if k= 0
                    then "let " & "Comp"&Text.From(k+1)&" = Table.AddColumn(#"&"""t"""&", "&""""&l{0}&" vs "&l{1}&""""&", each Value.Divide(["&l{1}&"], ["&l{0}&"])-1, Percentage.Type)"
                else
                    if k+1<n
                        then out & ",Comp"&Text.From(k+1)&" = Table.AddColumn(#"&""""&pstep&""""&", "&""""&l{0}&" vs "&l{1}&""""&", each Value.Divide(["&l{1}&"], ["&l{0}&"])-1, Percentage.Type)"
                    else out,
            Rest =
                if k+1<n
                    then @Comparisons(List.RemoveFirstN(l, 1), n, newouts, "Comp"&Text.From(k+1))
                else out & " in Comp"&Text.From(k)
        in Rest,
    RecBuild = (t as table) => [t = t, Table.AddColumn=Table.AddColumn, Value.Divide = Value.Divide, Percentage.Type = Percentage.Type],


    AddComparisons = Expression.Evaluate(Comparisons(YearList, List.Count(YearList)), RecBuild(#"Pivoted Years"))
in
    AddComparisons

Explanations:

From #"Max Price per Year" to #"Expanded Year Price" we get only data comparable, and the corresponding max for each year.

YearList helps us in Pivoting the years as columns.

Comparisons is a function that returns a query in text, with YearCount-1 steps, one for each comparison.

RecBuild is an easy function to build a record so we won't have to write a lot of things if we're calling it.

AddComparisons calls for the evaluation of Comparisons using RecBuild.

voila!

[Smauro · Accepted solution]

I had more fun.

Skipped some steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUfLJTM8oUYByHIHYyMDQEkgZGirF6uBTZWQAUmWMrMolsSgbpsgJYpQFmDIgoMoSQoFVBaWmwC2EsJ0RikxN8aoBu8nUgLAaM4hd7kWplUAelHJB8j1Y2r8oMS8d5Ew4wxVhhAnEmvCMzByQDIx2Q/jbEqgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Primary Color" = _t, #"Secondary Color" = _t, Item = _t, Year = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Primary Color", type text}, {"Secondary Color", type text}, {"Item", type text}, {"Year", type text}, {"Price", Int64.Type}}),
    YearList = List.Sort(List.Distinct(#"Changed Type"[Year])),
    #"Pivoted Years" = Table.Pivot(#"Changed Type", YearList, "Year", "Price", List.Max),

    Comparisons = List.RemoveNulls(List.Generate(() => [x = YearList, k = 0, y = null], each [k] < List.Count([x]), each [x=[x], k = [k]+1, y = [x]{[k]} & " vs " & [x]{[k]+1}], each [y])),
    GroupList = List.Transform(Comparisons, each {_, Expression.Evaluate( "each Value.Divide(List.Sum(["&Text.AfterDelimiter(_, "vs ")&"]), List.Sum(["&Text.BeforeDelimiter(_, " ")&"]))-1", [List.Sum = List.Sum, Value.Divide  = Value.Divide, Table.RowCount = Table.RowCount]), Percentage.Type}),
    #"Grouped Rows" = Table.Group(#"Pivoted Years", Table.ColumnNames(#"Pivoted Years"), GroupList)
in
    #"Grouped Rows"

All groupings before pivoting disappears --> this gives us all values, even non comparable ones

Now Comparisons generates a list of all possible comparisons

GroupList is the list read in the fake #"Grouped Rows" for the new columns.

[dax · Accepted solution]

Hi @zileng ,

You could refer to @Mariusz 's suggestions or try below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUfLJTM8oUYByHIHYyMDQEkgZGirF6uBTZWQAUmWMrMolsSgbpsgJYpQFmDIgoMoSQoFVBaWmwC2EsJ0RikxN8aoBu8nUgLAaM4hd7kWplUAelHJB8j1Y2r8oMS8d5Ew4wxVhhAnEmvCMzByQDIx2Q/jbEqgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Primary Color" = _t, #"Secondary Color" = _t, Item = _t, Year = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Primary Color", type text}, {"Secondary Color", type text}, {"Item", type text}, {"Year", Int64.Type}, {"Price", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US")[Year]), "Year", "Price", List.Max),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "2018 vs 2019", each [2018]/[2019]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "2019vs 20202", each [2019]/[2020]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Item] = "A" or [Item] = "B" or [Item] = "C"))
in
    #"Filtered Rows"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz · Accepted solution]

Hi @zileng 

Please see the attached file with a DAX solution, this can be done in Power Query but it's better to calculate it using DAX.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn