Price Variance by Category & Year

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.

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.

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!

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.